Help: Return the number of years and months between dates

[andrepr]

Seems simple but I just cannot get this to work. Have 2 date cells, Lease Starts, Lease ends. Need to return how many years, months & days between these dates, rounded up. Example: Lease Start: 23 Mar 2007; Lease Ends 27 April 2008. Result should be: 1 Year 1 Month 5 Days.

SO always round up years first, then count months and remining days.

 

[VoG]

Try

=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m " & DATEDIF(A1,B1,"md")+1&" d"

 

[andrepr]

Thanks for swift response!!!

Almost there... Used Start: 1 July 2003; End Date: 31 May 2013.

Result is 9 y 10 m 31 d.

Days should be rounded up to 11 months 0 days. If I change the date to 1 June result is 11 m 1d - How do I get zero days and correct months.

Same for years. If I change end date to 30 June 2013 - should be 10 years 0 months 0 days as result. Instead result is 9 y 11m 30 d

Any suggestion??

 

[VoG]

I think that the issue is that you want to return one more day that DATEDIF returns, which is why I used +1 in the formula above.

See if barry houdini's alternative formulas here http://www.mrexcel.com/forum/showthread.php?t=302028 yield your desired outcome