Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

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?

Re: Calculating the period between 2 dates

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/

With DATEDIF (Re: Calculating the period between 2 dates)

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.