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]

Brunts, I tried your function, but I feel it makes errors too.
I tried 30/01/2004 till 01/03/2004. One could argue that this is exactly one month, or that it's one month and one day.
Your function however sees it as one month and two days!

Another one: 30/09/1994 till 01/04/2006. 30/09/1994 till 30/03/2006 would be 11 years and 6 months. Add 31/03/2006 and 01/04/2005, so I'd expect 11 years, 6 months, 2 days.
Your function says 11 years, 6 months, 1 day.

 

[comecme]

I also found a case where consecutive days give the same output.
28/02/2007 - 28/02/2008 = 1 years, 0 months, 1 days
28/02/2007 - 01/03/2008 = 1 years, 0 months, 1 days

 

[barry houdini]

Brunts, I tried your function, but I feel it makes errors too.
I tried 30/01/2004 till 01/03/2004. One could argue that this is exactly one month, or that it's one month and one day.
Your function however sees it as one month and two days!

Another one: 30/09/1994 till 01/04/2006. 30/09/1994 till 30/03/2006 would be 11 years and 6 months. Add 31/03/2006 and 01/04/2005, so I'd expect 11 years, 6 months, 2 days.
Your function says 11 years, 6 months, 1 day.

DATEDIF also gives the same results in both cases. I think you can make an argument for that being correct.

DATEDIF works as if you're counting backwards, i.e. for the first example 1 month back from 1st March 2004 is 1st Feb 2004, then count back 2 days and you get 30th January.......

 

[mole999]

is that error replicated across other months or just the 28 days of Feb being made 30, does MS know something

 

[galileogali]

I have got Excel 2007 + Service Pack 1 for Office, both Spanish versions

Then

a1: 06/02/2008
B1: 03/03/2008
C1: SIFECHA(A1;B1;"d") = 26
I suppose: equivalent to the english DATEDIF(A1;B1;"D")

 

[galileogali]

Excuse me.
I had not understood the question with the third argument ;"md")

Please, delete both posts.

 

[galileogali]

Try this formula

Excel Workbook
	E	F	G
3	06/02/2008
4	03/03/2008		26
5
6	06/02/2004
7	03/03/2008		26
Hoja1

 

[comecme]

DATEDIF also gives the same results in both cases. I think you can make an argument for that being correct.

DATEDIF works as if you're counting backwards, i.e. for the first example 1 month back from 1st March 2004 is 1st Feb 2004, then count back 2 days and you get 30th January.......

In that example it's easy to see how DATEDIF gets its answer. But how about from 31th January 2008 till 28th February 2008? The DATEDIF answer is 26.
28th February, one month back = 28th January. Count 3 days ahead and you get 31th January. February 2008 has 29 days, subtract 3 from that and you get 26.
The same period in 2007 would yield 25 days. So, even though the lap day (29th February 2008) is not part of the date range, it does affect the outcome of DATEDIF.

This is very tricky stuff

 

[barry houdini]

When working backwards as I suggested you'd take the maximum number of full years without going past the start date, then the max number of the full months and then whatever days are remaining.

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 haven't tested that extensively, I imagine it doesn't work in all cases [ as per one of my earlier posts in this thread, there are some circumstances where DATEDIF can return negative numbers ]

I think there are some obvious bugs. The start point of this thread was that DATEDIF didn't correctly calculate in 2007 and there are others such as the -1s.

As for calculating the "correct" result, I think that simply depends on your definition. There will always be discrepancies when you try to measure using units of measurement which are variable in size, your only hope, I think, is to define exactly what the rules are, for what you want, and calculate from there, then all results will be "correct" within your definition.

 

[galileogali]

Barry:
Thanks ....
I thought I had a "simple solution".

Again, excuse me