Calculating the period between 2 dates

Posted by Len Clarke on November 08, 2001 2:06 AM

I need to calculate the period between 2 dates, with the answer shown as years and days e.g 3 years + 49 days.

I've looked at DATEDIF function but that doesn't seem to offer the solution I need.

Anyone got any ideas?

Posted by Robin on November 08, 2001 3:21 AM

If you dates are in cells A1 and B1 then:

=INT((B1-A1)/365)&" years"&" + "&(((B1-A1)/365)-INT((B1-A1)/365))*365&" days"

will give you what you want/

Posted by Aladin Akyurek on November 08, 2001 5:29 AM

=DATEDIF(A1,B1,"y")&" year(s) + "&DATEDIF(A1,B1,"yd")&" day(s)"

Or, a bit fancier:

=IF(B1-A1>=365,DATEDIF(A1,B1,"y")&" year(s)","")&IF(MOD(B1-A1,365),IF(B1-A1>=365," + ","")&DATEDIF(A1,B1,"yd")&" day(s)","")

These formulas require that B1>=A.

Aladin