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:

> You might want to reconsider using the DATEDIF function. It is an undocumented

> (and, thus, probably an unsupported) Excel function

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

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". 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 (I am presuming they need to be installed) or if they simply tap existing function functionalities. Either way, with the exception of XL2000 (if I remember correctly), Microsoft has not seen fit to document DATEDIF inside of Excel... I don't know why, but it makes me suspicious. So, I'll modify the boiler-plate language in my posting to acknowledge the link you posted, but I still think relying on a function with sketchy documentation history that Microsoft broke in a Service Pack release and has not gone back to fix it in a subsequent Service Pack or other update means is a risky venture. At the least, some people's Excel may work with formulas using DATEDIF and other's won't (those with SP2). That alone should scare people away from the function. On top of that, given that Microsoft did not attempt to modify DATEDIF would seem to indicate that it broke from a change made elsewhere in SP2... if a "leak-over" from changing a non-DATEDIF related function or functionality ended up breaking DATEDIF, then who is to say it won't happen again. That also should, I would think, scare people away from this function. This is just my opinion, of course. By the way, thanks for posting the link... I do appreciate having seen it.

Answer 3

Thank you, At home I am using XL2010 so will give it a try there. At this time work is using XL2007 SP2 so until they upgrade I will have to validate any critical time differences.

Answer 4

Rick wrote:

You might want to reconsider using the DATEDIF function. It is an undocumented (and, thus, probably an unsupported) Excel function

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

Answer 5

Thomas wrote:

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

Yes, it is a known defect with XL2007 SP1.  Seems to work fine in XL2010.  And I am not aware of any examples of DATEDIF(...,"md") that do not work in XL2010.