MD Days function returns wrong value

Question

For many years I have used the following formula to calculate the difference between an entered date & the current date in Years, Months, Days

=DATEDIF(C10,CURRENT_DATE,"Y")&" Years,"&DATEDIF(C10,CURRENT_DATE,"YM")&" Months,"&DATEDIF(C10,CURRENT_DATE,"MD")&" Days" 

The CURRENT_DATE references a cell containing =Today ()  For some reason the days are now returning incorrect such as 123 Days instead of the actual days since the prior month. Example: 5/31/2011 as entered date computed off current date 01/10/2012 returns 0 Years, 7 Months, 123 Days

Answer 1

I know others disagree with me on this, but I would recommend not using DATEDIF, especially if the worksheet will be used for something important. Here is a post I have given in the past explaining why I am making this recommendation...

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. Someone recently posted this message as part of a newsgroup question...

*********************************************************************

=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In XL2003, the above formula gives me the correct answer of 9. However,

in Excel 2007, it gives me 122. The 122 increases in value until it

hits 143 on 1/26/2012 and then, on 1/27/2012, the difference becomes 0.

*********************************************************************

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 an unsupported) function, the odds of Microsoft spending the time to search down and fix whatever broke is slim. In addition, again because it is probably unsupported, the extent of any future breakage in the function due to other code change Microsoft makes elsewhere in Excel is unknowable... something that works today may not work tomorrow and Microsoft will probably never fix it. 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.

ADDITIONAL FOLLOW-UP

---

There is an indication that this might have been fixed in XL2010, however it remains broken in XL2007 as SP2, so if you are in a mixed environment of these two versions, you would be asking for trouble to continue using it. And even if you move wholly to XL2010, there is always the "they broke it once so what would stop them from breaking it again" possibility. Personally, I am still recommending that DATEDIF not be used, but the final decision as to how much you are willing to risk your data to this undocumented function is up to you.

Answer 2

Rick wrote:

I presume you showed me that because it is "documentation" for the DATEDIF function. I note that the "applied to" list is "Microsoft Office SharePoint Server 2007" and "Windows SharePoint Services 3.0".

Yes, Microsoft documentation is often incomplete and even wrong in details [1].

Rick wrote:

I am not familiar with either of these products so I don't know if Office installations inherit any additional function functionalities by installing either or both of these

It is the other way around.  SharePoint relies on the Office suite (Excel etc) for functionality.  From one SharePoint whitepaper (click here):

"Microsoft Office 2010 with Microsoft SharePoint 2010 gives people the ability to seamlessly view and edit information from a PC, browser, and smartphone4.  Microsoft SharePoint Workspace 2010 (formerly Groove) makes it easier for people to take information offline, then resynchronize when they reconnect to the network.  [....] The new Microsoft Office Backstage™ view helps to surface SharePoint 2010 capabilities in the context of Office applications, including greater automation of metadata capture and streamlined access to document libraries and SharePoint sites".

In any case, note that the URL I provided is office.microsoft.com/..., not sharepoint.microsoft.com/... .

I wrote:

See http://office.microsoft.com/en-us/help/datedif-function-HA001160981.aspx.

The important take-away from that help page is:  "This function is provided for compatibility with Lotus 1-2-3".

So yes, this is legacy functionality.  And as such, it is certainly an endangered species, ripe for extinction.

But that does not mean it is not supported.  I cannot say one way or the other with impunity.  Besides, the Microsoft definition of "support" is dubious.  There are some defects in indisputably "supported" functionality that never get fixed.

In any case, I am not trying to influence, advise or argue with people on the usage (or not) of DATEDIF.  To each his/her own.

It is certainly fair to point out that DATEDIF(...,"md") is unreliable in XL2007, which is exactly what I said at the outset.

---

[1] Case in point....  The DATEDIF help page states:  "By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900".

Yes, Jan 1 2008 is number 39448.  But that is because it is 39448 days after Dec 31 1899, not Jan 1 2008.  Jan 1 2008, not Dec 31 1899, is number 1 in Excel.

FYI, I suspect the source of the misinformation is from documentation related to VBA, where Dec 31 1899 is indeed number 1(!).