DateDif

[Rudi]

Someone mentioned to me today that DATEDIF is not accurate anymore since Excel 2010? So I started a search for any articles which could verify this and I found nothing really to prove this claim...though I did find a thread at Ozgrid that is a bit concerning? Testing it in Excel 2013 with the same dates but in the current year still gives the 2 and 2 month output. I use DATEDIF regularly in spreadsheets, and have never had issues with it, but I'm wondering now if the times when I used it, it has been giving me good results? Seed of doubt now...????

1. Has anyone come across any chat regarding the inaccuracies of DATEDIF since ver. 2010
2. What is happening with the month output between the dates: 30 Nov 2013 and 28 Feb 2014?

TX.

[HansV]

Whether that is a bug is debatable. The programmers used a relatively simple algorithm: if the day number of the second date is less than that of the first date, subtract 1 from the number of months.

I think you'll agree that the difference in months from 30-Jul-2014 to 29-Sep-2014 should be 1: it's less than 2 whole months since 29 < 30.

For 31-Jul-2014 to 30-Sep-2014, DATEDIF uses the same reasoning and also returns 1, since 30 < 31. As humans, however, we reason that the result should be 2, since the entire months of August and September have passed between the two dates.

If this bothers you, you could use

=DATEDIF(A1,B1,"m")+AND(DAY(A1+1)=1,DAY(B1+1)=1)

to calculate the difference in months between the dates in A1 and B1.

There were more serious problems with DATEDIF in Excel 2007 SP2, which were corrected in Excel 2010 and later: see Recommendation: Do not use the undocumented DATEDIF function

[Rudi]

TX for your insight on the way it calculates; that is interesting to learn. I'm quite happy to continue using it, but will probably double check (or veer to other functions) if the returning value needs to be perfectly accurate. Fortunately this does not occur often.