# Excel - DATEDIF issue

howryou 19:39 22 May 2008
Locked

Two issues
1-Getting an issue where the difference between two dates are the same
2- Getting an issue where the result is a negative number of days

ISSUE 1
Value in Cell D1 = 22/05/2008
Value in Cell A4 = 31/01/1970
Value in Cell A5 = 01/02/1970

Formula in Cell B4 = =DATEDIF(A4,\$D\$1,"Y") & " Years, " & DATEDIF(A4,\$D\$1,"YM") & " Months, " & DATEDIF(A4,\$D\$1,"MD") & " Days" which results in 38 Years, 3 Months, 21 Days

Formula in Cell B5 = =DATEDIF(A5,\$D\$1,"Y") & " Years, " & DATEDIF(A5,\$D\$1,"YM") & " Months, " & DATEDIF(A5,\$D\$1,"MD") & " Days" which also results in 38 Years, 3 Months, 21 Days

ISSUE 2
Value in Cell D1 = 01/03/2006
Value in Cell A4 = 30/01/1970
Value in Cell A5 = 31/01/1970

Formula in Cell B4 = =DATEDIF(A4,\$D\$1,"Y") & " Years, " & DATEDIF(A4,\$D\$1,"YM") & " Months, " & DATEDIF(A4,\$D\$1,"MD") & " Days" which results in 36 Years, 1 Months, -1 Days

Formula in Cell B5 = =DATEDIF(A5,\$D\$1,"Y") & " Years, " & DATEDIF(A5,\$D\$1,"YM") & " Months, " & DATEDIF(A5,\$D\$1,"MD") & " Days" which also results in 36 Years, 1 Months, -2 Days

Is DATEDIF as flakey as it appears or am I doing something wrong. What is the alternative way of determining Differences between dates accurately

Thanks,

VoG II 20:05 22 May 2008

howryou 21:52 22 May 2008

However, the second formula is also not perfect,

Between the dates of 28/02/1970 and 22/05/08
is 38 years 2 months 24 days

but just one day later, between the dates 01/03/1970 and 22/05/08 is 38 years 2 months 21 days

then between 30/03/1970 and 22/05/08
is 38 years 1 months 22 days and then one day later on 31/03/1970 is also is 38 years 2 months 21 days

An imperfect solution

VoG II 09:41 23 May 2008