Bug in DATEDIF in Excel 2007?

[VoG]

Please see the small table below produced using XL 2007

22 12 25.xlsm
	A	B	C
1	6/02/2008	3/03/2008
2
3	26	26	0 Years 0 Months 28 Days
Sheet2 (2)

Cell Formulas
Range	Formula
A3	A3	=B1-A1
B3	B3	=DATEDIF(A1,B1,"d")

Formula in C3:

=DATEDIF(A1,B1,"y") & " Years " & DATEDIF(A1,B1,"ym") & " Months " & DATEDIF(A1,B1,"md") & " Days"

In Excel 2000 (at least) all three formulas yield the correct result of 26 days.

Is this a known bug with DATEDIF in XL 2007?

More to the point, is there an alternative formula that will give the equivalent (but correct!) output to the one in C3 in the example?

 

[jimihndrks]

Thank you, that is much shorter. I added on &" days" to the end of it as well. Thanks again.

 

[mirandamm]

Workaround for DATEDIF Bug in Excel 2007 (Windows 2010)

Was led to this forum via a Google search for “DATEDIF bug.” In some instances, the DATEDIF function was giving me a large number of days (between 114 and 143 instead of between 1 and 30), so wanted to know if anyone had devised a solution. Unable to find one posted, came up with a workaround which I’d like to share in case it’s helpful to others until Microsoft fixes the bug.

In the function DATEDIF(date1,date2,interval), the error happens only when date2 is in a leap year; the day of date2 is less than the day of date1; e.g., date1 is January 26, 2010 and date2 is January 25, 2012; and the interval is “md” (number of days excluding the months). The workaround is that, if an error is found; i.e., if the resulting number of days excluding months is greater than 30, for the days interval of the DATEDIF computation only, add 365 to date2 so the year portion of the date is no longer a leap year while the month and day portions remain unchanged.

With a past date in cell A1, the following formula provides the difference between today and the date in cell A1, omitting the year, month, and day values that are equal to 0, in the format “x years y months z days.”
=IF(DATEDIF($A2,TODAY(),"y")=0,"",DATEDIF($A2,TODAY(),"y")&" years ")&IF(DATEDIF($A2,TODAY(),"ym")=0,"",DATEDIF($A2,TODAY(),"ym")&" months ")&IF(DATEDIF($A2,TODAY(),"md")=0,"",IF(DATEDIF($A2,TODAY(),"md")>30,DATEDIF($A2,TODAY()+365,"md"),DATEDIF($A2,TODAY(),"md"))&" days")

Thanks!

MMiranda
Miami, Florida, USA

 

[AddinBox_Tsunoda]

Knowledge Base ( KB2678753 ) about this malfunction was released on 20-Feb-2012.
(It is only a Japanese edition. The English edition is not yet released.)
http://support.microsoft.com/kb/2678753/ja

-- Symptoms --
When unit is YD or MD, and End_date accords in the following condition in Excel2007(SP3) ,
the result of DATEDIF becomes 113 many values than a right value.

Leap year (2000, 2004, 2008, 2012, etc)
and  January
and  [ Day (Start_date) > Day (End_date) ]

Notes : A condition and a symptom of the malfunction are different from the above in the SP3 non-application.

-- Workaround -- ( Requirement: Apply Service Pack 3 to Excel2007. )

[YD]
=IF(AND(INFO("release")="12.0",
            MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1))
     ,DATEDIF(A1,B1,"YD")-113, DATEDIF(A1,B1,"YD"))

[MD]
=IF(AND(INFO("release")="12.0",
           MONTH(DATE(YEAR(B1),2,29))=2,MONTH(B1)=1,DAY(A1)>DAY(B1))
     ,DATEDIF(A1,B1,"MD")-113, DATEDIF(A1,B1,"MD"))

A bug does not completely disappear.
A bug of DATEDIF in before Excel2003 and Excel2010 is left.

 

[Rick Rothstein]

I know this started as an old thread, but I see some add to it relatively recently, so I figured I would as well.

I think the UDF code I posted in my blog article "UDF (user defined function) replacement for Excel's DATEDIF function" works correctly as a replacement for Excel's flawed DATEDIF function.

Here is an article I wrote regarding the DATEDIF function itself: "Recommendation: Do not use the undocumented DATEDIF function".

 

[AddinBox_Tsunoda]

I introduce my UDF(ktDATEDIF) substituting for DATEDIF.
http://www.h3.dion.ne.jp/~sakatsu/Excel_Tips05_E.htm


This UDF is based on "how to count based on the law of Japan".
(I place it in the page mentioned above)

Please use it after having determined whether you accord with way of counting in your country.