Date split as per the Tenure

[fareedexcel]

Dear Experts,

I have a query on splitting the no. of years into Years, Months and Days.

For Example -
Date of Joining - 1-Jan-16.
Last Working Date - 10-Oct-18.
Tenure - 2.78 Years which needs to be splitted in 3 rows 2 years 9 months 10 days

1st row - 1-Jan-16 to 31-Dec-17 - 2 Years
2nd row - 1-Jan-18 to 30-Sep-18 - 9 Months
3rd row - 1-Oct-18 to 10-Oct-18 - 10 days

Again the challenge is, if DOJ - 1-Jan-16 and LWD - 31-Dec-19
there should be only one row,

1st row - 1-Jan-16 to 31-Dec-19 - 4 years

-------------------------------------------------------------------------------------------------

sample file is attached for reference. Kindly help

 

[Hui]

F7: Years: =DATEDIF(B4,D4+1,"Y")
F8: Months: =DATEDIF(B4,D4+1,"ym")
F9: Days: =DATEDIF(B4,D4+1,"md")

Copy down to other ranges

 

[fareedexcel]

But the above code is not splitting the data in rows as per the tenure. The requirement is as per the tenure the data should be split across the rows as per the scenarios mentioned in the attached sheet

 

[fareedexcel]

anyone can help on my requirement. I have attached the sheet in my 1st mail