Thread: Generate total elapsed time with DATEDIF

Can somebody explain what is missing here? Why is the elapsed time so far off with only a change in 1 months times. I can't see what it is but it appears to be happening across 2012. Am I just overlooking something obvious?

A1 = 9 Oct 67
B1 = 12/31/2011
C1 = 44 years 2 months 22

Formula:

=DATEDIF(A1,B1,"y")&" years "&
    DATEDIF(A1,B1,"ym")&" months "&
    DATEDIF(A1,B1,"md")

A2 = 9 Oct 67
B2 = 1/1/2012
C2 = 44 years 2 months 136

Formula:

=DATEDIF(A2,B2,"y")&" years "&
    DATEDIF(A2,B2,"ym")&" months "&
    DATEDIF(A2,B2,"md")

I can't replicate your problem using 2003. (I don't have access to 2007 at the moment.)
I get C2 = 44 years 2 months 23 which would seem to be correct.

There are known bugs with DATEDIF() in 2007, but I don't recognise that one, I can't see how it can return > 31 using "md".
This is an unsupported function so microsoft aren't going to be of much help.

Can you post your worksheet?

Here's something I found in this link Contextures Blog - Calculating Ages in Excel

Rick Rothstein (MVP - Excel)

February 1, 2010 at 4:10 am · Reply

From a previous newsgroup posting of mine...

You might want to reconsider using the DATEDIF function. 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. And even if Microsoft did fix the problem in a subsequent Service Pack, any of your users who remained at SP2 would be subjected to incorrect result.

Thanks Marcol. The UDF, ktDATEDIF worked great and I do have the XL2007 with SP2.

Originally Posted by Marcol

....This is an unsupported function....

I'm not sure that Microsoft have ever indicated that DATEDIF is unsupported, indeed there was a "fix" in SP2 for the "ym" version of DATEDIF (which is probably what broke the "md" version!)

I made an attempt once before to document various DATEDIF problems.

I'll try again here.....

First the good news. Of the 6 possible options for argument 3, i.e. "y", "m", "d", "ym", "yd" and "md", the first 4, AFAIK all work OK in all excel versions since 2000 at least [or they work as intended, some may argue with the results]

DATEDIF with 3rd argument "md", though, produces negative results (-1 or -2 in some cases), e.g. this formula

=DATEDIF(DATE(1996,8,31),DATE(1998,3,1),"md")

gives me the result -2 in 2003, 2007 and 2010. It seems to me that for those two dates you should get the result "1 year, 6 months, 1 day", i.e. result for "md" should be 1 not -2

In Excel 2007 2 new errors crop up - the problem that Jeff reports here and that Rick Rothstein comments on, whereby "md" result is higher than would normally be expected (you wouldn't expect any result > 30 IMO)....and also a problem with "yd", whereby this formula:

=DATEDIF(DATE(2002,1,19),DATE(2004,1,14),"yd")

gives me a result of 473 (again, you wouldn't expect a result > 365 for "yd")

This latter problem was fixed in SP2, I believe, or it was according to MS - the only comment in the documentation for SP2 is - The DATEDIF function returns the wrong result when the option (third) parameter is "yd."

In Excel 2010, at least for me, these latter problems don't seem to exist, although I still get the negative numbers with "md".

An alternative formula approach, given that the only problematic part of Jeff's formula is the 3rd DATEDIF function

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))

...or shorter with EDATE.....

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&B1-EDATE(A1,DATEDIF(A1,B1,"m"))