Is Excel datedif formula is not correct enough?

Question

I used datedif but I fount that it's results are not enough correct.
So I tried to make a function to get the best results.
I how it here to test it and tell me is it good or not and why?
this is the link to my file
http://www.4shared.com/document/2LUs22rF/mas\_date\_diff.html

Answer 1

DateDif (worksheet function) has a bug since Off 2007 SP2.

Please read this post http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-to-change-number-of-days-to-yearsmonths-and/d711c71e-8b71-4311-b568-57b6b4eee504

and particularly the comments from Rick Rothstein, MVP - Excel

on the subject

The VBA function DateDif is still correct.

Regards

JY

It works correctly in my Excel 2010 SP1. But it might be better not to depend on DATEDIF.

Answer 2

Old date : 29/12/2007

New date : 28/2/2008

Datedif result is : 0 y 1 m 30 d

Try the next old day : 30/12/2007

Datedif result : 0 y 1 m 29 d

Remark that we don't calculate the leap year day: 29/2/2008

Where is the wrong??

I don't see a gap - the start date is 1 day later so the number of days is 1 less. The calculation is entirely correct. There is nothing wrong.

Answer 3

It's a possibility, but it has the disadvantage that I mentioned: according to your way of calculating there is a gap of 3 days

20-Aug-1977 to 28-Feb-2011 = 33 years, 6 months, 8 days

20-Aug-1977 to 01-Mar-2011 = 33 years, 6 months, 12 days

One day later causes an interval of four more days. That looks strange to me. But if you prefer to do it that way, it's your choice.

Using the built-in DATEDIF function, the calculation from 20-Aug-1977 to 16-Mar-2011 works out as follows:

20-Aug-1977 to 20-Aug-2010 = 33 years = 33*365 + 8 (leap years) = 12053 days.

20-Aug-2010 to 20-Feb-2011 = 6 months = 31+30+31+30+31+31 = 184 days.

20-Feb-2011 to 16-Mar-2011 = 8+16 = 24 days.

Total 12053+184+24 = 12261 days.

Answer 4

Thanks dear,
but
if you see the total days between the two dates as it in my file
you will see that:
the left days to the end of August is 11 days. aren't it?!
the left days of March is 16 days. aren't it?!
the total days are 11+16=27
the complete months between the two dates are 6 months
and the total days in the 6 months are 181 days. aren't it?!
so the total days of days and months are 181+27=208
let's see the complete years and it's day
years are 33 and the days as described in my file are 12053 with attention to the year with 29 days February.
so the total days between the two dates are
208+12053=12261
and this is total days if you calculate
=a2-a1
.........
what's your opinion about this calculations?
is it true or false?!!

Answer 5

In your example, the rationale behind DATEDIF is:

From 20-Aug-1977 to 20-Aug-2010 is 33 whole years.

From 20-Aug-2010 to 20-Feb-2011 is 6 whole months.

From 20-Feb-2011 to 16-Mar-2011 is 24 whole days.

This is a valid way of calculating the number of years, months and days, and I suspect it's the way most people would calculate it.

With your way of calculating, from 20-Aug-1977 to 28-Feb-2011 is 33 years, 6 months and 8 days. This is the same result as DATEDIF yields.

But from 20-Aug-1977 to 01-Mar 2011, your function results 33 years, 6 months and 12 days. The end date is only one day later, but you jump from 8 days to 12 days. I think the majority of people wouldn't agree with that.

DATEDIF would return 33 years, 6 months and 9 days, a jump of 1 day, which seems more reasonable.