DATEDIF vs. simple difference

[wal]

Excel 2007 (also 2003)

For the number of days between March 30, 2009, and April 1, 2009 (for
example), the following formulas give the following results:

=B6-A6 >>>> 2 days
=DATEDIF(A6,B6,"d") >>>> 2 days
=DATEDIF(A6,B6,"md") >>>> 1 day

I didn't realize this until I happened to fill in two dates that
crossed two months but were less than one month apart. My actual
formula is the following, which I thought would safely cover date
differences of any length:

DATEDIF(A2,B2,"y") & " year(s), " & DATEDIF(A2,B2,"ym") & " month(s),
" & DATEDIF(A2,B2,"md") & " day(s)")

The problem is the "md" argument in the day component. Chip Pearson
at http://www.cpearson.com/excel/datedif.aspx provides the definition
of "md" as "Complete calendar days between the dates AS IF THEY WERE
of the same month and same year" (apparently from the Excel 2000 Help
files). What does that mean --- "as if they were"?

If you substitute in my long formula above "d" in place of "md", you
get a poor result when the dates are farther apart: e.g., March 12
2008 to April 6 2009 gives the result 1 year, 1 months, 419 days.

In short, you can't use my long formula above as a foolproof general
way to calculate date differences, because if your result is less than
a month but happens to encompass two calendar months, you'll get an
incorrect figure.

I guess the safest solution is to have an embedded "IF" function: If
year difference is 0 and month difference is 0, simply subtract the
dates; otherwise, use the long formula above -- ??

Thanks for any comments.

 

[Rick Rothstein]

The DATEDIF function seems no longer to be a useful function to use. It is
an undocumented (and, thus, probably an unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this message as part of a newsgroup question...

**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward.

 

[Lars-Åke Aspelin]

This problems seems to have something with leap year to do.
In Excel 2007 SP2, the correct answer (9) is given when the second
year is not a leap year and incorrect when the second year is a leap
year.
I tested from 2012 upto 2412 an the correct answer is given for all
non leap years, including years 2100, 2200 and 2300.

Best is to avoid this function I guess. / Lars-Åke

 

[Lars-Åke Aspelin]

In the list "Issues Fixed by Service Pack 2 (SP2) in the 2007 Office
Desktop Applications"
http://download.microsoft.com/downl...A418F/2007 Office Service Pack 2 Changes.xlsx
a fix of the DATEDIF function is actually mentioned, so it seems
Microsoft at least gives some attention/support to this function.

Excel The DATEDIF function returns the wrong result when the option
(third) parameter is "yd."

Maybe that fix gave cause to the new problem with "md".

Lars-Åke

 

[Rick Rothstein]

Even if Microsoft fixes the problem at Service Pack 3, DATEDIF cannot really
be offered as a solution anymore just in case a user is at or, in the
future, stays at Service Pack 2. Unless, perhaps, Microsoft includes the fix
with one of its Office updates; but even then I'm not sure because not all
users opt for these interim updates.

 

[Rick Rothstein]

Interesting, but it did work prior to SP2... I wonder what they did to take
a working functionality and break it?