DATEDIF "md" 2007 issue?

[Haddox]

Looking through various forums, I see that the DATEDIF function can be a bit
buggy at times, particularly w/respect to "md" interval type and in Excel
2007.

So I just wanted to see if folks could replicate these strange results in
2007, and perhaps confirm this is indeed a bug:

=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. Have not tried it in 2003 yet.

Appreciate any responses. Thanks.

 

[Jacob Skaria]

Should be 9.

It is time to review.
http://www.cpearson.com/excel/datedif.aspx

If this post helps click Yes

 

[Rick Rothstein]

That's odd... I get 9 in XL2003 and I get 122 in XL2007.

My XL2007 is at SP2... what service pack level are you at?

 

[Haddox]

Thanks for the responses, everyone. My 2007 is on SP2.

Also, is there a system whereby we can notify MS of this type of event (if
indeed it is a bug)?

 

[Haddox]

Appreciate it. I actually started there. The problem, as originally stated,
is that it is resulting in 122 in Excel 2007, but apparently not in other
versions.

 

[Ron de Bruin]

Hi Haddox

No Sp and SP1 are working OK

Sp2 is the problem

Also, is there a system whereby we can notify MS of this type of event (if
indeed it is a bug)?

Rick or I will report it but I think it will never be fixed because this
formula is not supported by Microsoft.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Rick Rothstein]

As I have not had an occasion to report anything in the past, I'm not really
sure of what the procedure is; so, if you wouldn't mind, I'll let you report
it. Thanks.

As for getting it fixed (assuming Microsoft would want to even do that with
an unsupported function)... any fix will come too late. It appears that the
bug was introduced in Service Pack 2... I guess the next opportunity to fix
the bug wouldn't come until Service Pack 3, and who knows when that will be.
On top of that, not everybody updates to the latest Service Pack as soon as
they come out (look at the OP... he didn't even install SP1), so there will
be a lot of SP2 versions of XL2007 out there for what will probably be a
"long time". I guess this means we volunteers here will have to stop
recommending the use of DATEDIF from now on (who knows what else got screwed
up besides the problem the OP raised). Now, if we could only retract all
those previous recommendations to use DATEDIF that we all have posted in the
past (and which are archived all over the place).

 

[Ron de Bruin]

As I have not had an occasion to report anything in the past, I'm not really

sure of what the procedure is; so, if you wouldn't mind, I'll let you report
it. Thanks.

No problem Rick, I will report it on Connect for you

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Haddox]

Also, Rick - strange (or maybe not), but swap out any month/year and you get
the same result. For example:

=DATEDIF(DATE(1956,4,27),DATE(2012,1,5),"md")

=DATEDIF(DATE(2011,12,27),DATE(2012,1,5),"md")

Perhaps related to that end date? Any ideas or source of further info I can
contact would be appreciated.

 

[Haddox]

Many thanks to both you and Ron for taking the time to reply and report this
error. I just sent an interim response before I realized you'd responded.
This reply stated that the error seems to center around that end date range
only (as far as I could tell).

Anyway, thanks again.

As a P.S. : the cpearson link did have the following quote which
underscores Ron's comments:"DATEDIF is treated as the drunk cousin of the
Formula family."