Excel - DATEDIF issue

  howryou 19:39 22 May 08
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 08

There is a bug. See this earlier discussion click here and click here

  howryou 21:52 22 May 08

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 08

I can't claim any great expertise on dates in Excel. I suggest that you add to my thread on MrExcel (link above) with your examples. You might find click here useful for posting a shot of your examples.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac