datedif; what's up?
cate
mysterious nature (undocumented?). Does anyone else use it?
Excel2003+
Dave Peterson
Rick Rothstein
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.
--
Rick (MVP - Excel)
"cate" <catebe...@yahoo.com> wrote in message
news:197d3b8f-a74c-4e15...@r1g2000vbn.googlegroups.com...
Joe User
news:ubDacemf...@TK2MSFTNGP06.phx.gbl...
> 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.
Just because MS failed to document it, that does not mean it is unsupported.
Indeed, the very fact that its behavior changed in Excel 2007 SP2 might
suggest that someone is diddling ("supporting") it. Of course, on the other
hand, it might simply mean that something else changed internally, and no
one bothered to correct DATEDIF for the change.
In any case, I thought MVPs have special access to the MS technical staff.
Surely by now, some MVP has contacted them about DATEDIF and gotten the
straight poop on it: supported or not; to be fixed or not; deprecated or
not?
What is MS's answer to those questions?
----- original message -----
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:ubDacemf...@TK2MSFTNGP06.phx.gbl...