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?

 

[comecme]

...
For 31st January 2008 to 28th February 2008 you can't take any full years or months so the answer is just 28 days, which is the same result DATEDIF gives. this works exactly the same for 2007, as expected...

I would expect 28 to be the answer. But my Excel 2007 gives me 26 days.

I'm using Excel 2007 (12.0.6325.5000) Dutch

 

[VoG]

Barry - if you are still following this thread then this may be of interest http://www.mrexcel.com/forum/showthread.php?t=415752

 

[rabelardo]

greetings.

I tried the formulae you gave and only 1 showed a logical answer: =B1-A1

I'm using MS Excel 2007 as well (Office Enterprise).

solution.

you could either:

* switch the contents of A1 & B1 or

* change the Datedif formula in such a way that the earlier date is entered 1st in the argument followed by the latter date (reference date should be entered chronologically).

Hope this helps.

 

[rabelardo]

oh, btw, i didn't get 26 as the number of days.

instead i got 91 as the difference between 06/02/2008 & 03/03/2008.

 

[Peter_SSs]

oh, btw, i didn't get 26 as the number of days.

instead i got 91 as the difference between 06/02/2008 & 03/03/2008.

That would probably be because I'm guessing your dates are in mm/dd/yyyy format so 06/02/2008 is 2 June 2008 whereas you might note the 'location' of VoG, who originally posted, is the UK so his dates are in dd/mm/yyyy format, meaning 06/02/2008 is 6 February 2008.



That is also why he wouldn't swap A1 with B1 in his formula since they are already in the correct order:

* switch the contents of A1 & B1 or

* change the Datedif formula in such a way that the earlier date is entered 1st in the argument followed by the latter date (reference date should be entered chronologically).

 

[jimihndrks]

I used the following formula to get the length of service for my employees. I am using two fields...one with their full time date and one for the current date. It seems to work fine except the "days" that show have 12 decimal places. How do I change this so the days do not have decimals?? I've tried removing or changing the "12" below but that doesn't work...just changes the month total.

=YEAR($A$3)-YEAR(N5)-(TEXT($A$3,"mmdd")< TEXT(N5,"mmdd"))&" years "&MOD(MONTH($A$3)-MONTH(N5)-(DAY($A$3)< DAY(N5)),12)&" months "&$A$3-MIN(DATE(YEAR($A$3),MONTH($A$3)-(DAY($A$3)< DAY(N5))+{1,0},DAY(N5)*{0,1}))&" days"

 

[Peter_SSs]

How is the 'Current Date' field populated?

 

[Andrew Poulsom]

I expect the date in A3 includes a time. For today's date use =TODAY() instead of =NOW().

 

[jimihndrks]

Yes I was using the =NOW() instead of the =TIME(). I am completely self-taught but I've been doing it a while. These are the simple things I miss by just not having run across it in the past. It did work. Thank you.

 

[barry houdini]

This version uses DATEDIF but should avoid the DATEDIF bugs discussed in this thread....it also allows A3 to be =NOW() or =TODAY() as required

=DATEDIF(N5,$A$3,"y")&" years "&DATEDIF(N5,$A$3,"ym")&" months "&INT($A$3)-EDATE(N5,DATEDIF(N5,$A$3,"m"))