The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives.
How Many Years, Months and Days Has it Been?
The following spreadsheet works well for birthdays or anniversaries. The DATEDIF Function generates the Years, Months, and Days.
The End Date column has the formula =TODAY().
The Years heading in cell D2 has the custom format ;;;”Years” which shows the text “Years” in the spreadsheet cell while hiding the underlying value “y” that you can see in the formula bar.
The headings for Years, Months, and Days all have a custom format. The Underlying Value is what’s essential for the next section.
Using the DATEDIF Function
The DATEDIF function is present in all current versions of Excel, but is only documented in Excel 2000. It was provided for compatibility with Lotus 1-2-3.
The Syntax is:
DATEDIF(start_date, end_date, unit)
Start Date and End Date are self-explanatory, although the end date must come after start date. The Unit is the type of information you want to return.
The formula in cell D2 is =DATEDIF($B2,$C2,D$1) where $B2 is the Start Date, $C2 is the End Date, and D$1 is the Unit. This gives you =DATEDIF(“10/2/1987”, “1/11/2011”, “y”) and returns 23 years.
I’m using the unit argument “ym” in the Months column to return the number of months. (That’s the value in cell D1 you can’t see in the spreadsheet). The unit argument is “md” for the Days column. The formulas in row 2 are:
- Years =DATEDIF($B2,$C2,D$1) = DATEDIF(“10/2/1987″,”1/11/2011″,”y”)
- Months =DATEDIF($B2,$C2,E$1) = DATEDIF(“2/21/1978″,”1/11/2011″,”ym”)
- Days =DATEDIF($B2,$C2,F$1) = DATEDIF(“9/27/1975″,”1/11/2011″,”md”)
A Problem with DATEDIF Function
I used a column of Start Dates for the past year and noticed a pattern with the Days column. Every month in the DATEDIF Function seems to have 31 days.
The 31-Day Cycle
The first pair of dates are the 11th and 12th of 2010. The first date is one year, zero months, and zero days from 1/11/2011. The second date is zero years, 11 months, and 30 days from 1/11/2011.
The green pairs show the Days cycle from zero to 30 days, which means a 31-day cycle.
Missing Days
Now, look at the red pairs of data. These are the only non-sequential Days returned by the DATEDIF for the past year.
The first pair has start dates of 2/28/2010 and 3/1/2010. The first date is zero years, ten months, and 14 days from 1/11/2011. The second date is zero years, ten months, and ten days from 1/11/2011.
Three days are missing from this one day sequential difference.
The rest of the red data pairs skip one day, the day after a month with less than 31 days.
For the spreadsheet I’ve put together, this DATEDIF anomaly will not make me lose any sleep. Missing a day or two in 10 to 50 years is not very bothersome.
Hi Gregory
I have been looking at your explanation of the anomily with DATEDIF and I think that perhaps your interpretation of the what the ‘MD’ unit is doing is incorrect.
The ‘MD’ unit returns the number of days since the last completed month not the difference in days excluding the months and years, as you have explained above.
In your example you will notice that all of your non sequential values occur with dates that are the first of the month following a month with less than 31 days.
The reason for the difference is an additional month has been competed and the number of days since the last completed month is calculating from a different point in time (date).
using
02/28/2010 to 01/11/2011
Last completed month = 12/28/2010
Number of days between 12/28/2010 and 01/11/2011 = 14
Using 03/01/2010 to 01/11/2011
Last completed month = 01/01/2011
Number of days between 01/01/2011 and 01/11/2011 = 10
The formula is working exactly as intended.
Cheers
Andrew
@Andrew, nice try but I’ve got Excel 2000 and the DATEDIF function is documented by Microsoft in Help so I’ll quote what they write for the “MD” definition:
“The difference between the days in start_date and end_date. The months and years of the dates are ignored.”
So it’s not my interpretation of the DATEDIF function but that of Microsoft. And that is wrong.
I’m not sure I understand what you mean regarding “an additional month has been completed.” All I know is that the number of days between 12/28/2010 and 01/11/2011 = 11 and the point of the article was to point out that there’s some error in the DATEDIF function. It will work if you’re calculating a large number but you don’t want to bet your life on its precision.
I agree that the DATEDIF function is recalculating every month when the last day of the month isn’t 31 but I gave up trying to figure that out. Too much brain power generated at that point int time and I needed that rare, stiff drink to forget the whole matter.
@Gregory
LOL Microsoft aren’t really sure how it is supposed to work. I am guessing that they just copied the function from Lotus 123 years ago (before the internet so no docco) and made some assumptions.
I have tested using the logic that ‘MD’, ‘YM’ and ‘YD’ return the number of days, months or days since the last completed month or year as the case may be and the function works perfectly every time.
I do agree with you on the too much brain power comment. I think I need to get over this and get back to work.
Cheers
comparing all dates between 1/1/2008 and 7/29/2012 with today’s 7/29/2012, using “MD”:
>> for all 29s (Jan.29, Feb.29, Mar.29 etc.) the result is 0
>> no day has 30 or 31 as result
>> for all 30s the result is 29
>> for all 31s _AND_ all 1s the result is 28
please reformulate the logic behind all 1s and 31s regardless of month have the same result of 28.
I didn’t realize the day of the month would make such a difference. This is why I said the function is flawed and a good reason it’s only documented in Excel 2000.
To use this function in precise calculation, it is necessary to completely understand its flaws so they can be corrected.
Even though I calculate manually as Andrew for those infamous 10 and 14 days, I still don’t know what to do with 1s and 31s = 28 days or with some negative results (when comparing 1/31/2012 with 3/1/2012 the result is -1).
Right now I’m trying to rewrite the easy “md” with a combination of IFs and DATEDIF (“d”, “m” and “y” which seems to be reliable), but a UDF is required in the end.
Well, I’ll be.
Seems that the formula simply counts a completed month as the total days in
advance from the day of start_date.
01/31/2012 plus 31 days = 03/02/2012 (last completed month)
days from last completed month (03/02/2012) to end_date (03/01/2012) = -1
For non leap years the answer will be -2 and using either 1st or 2nd of March in any year will always result in a negative. For months with 31 days followed by 30 days looks you will see the same result for the 31st and 1st.
I guess that is why you don’t see lotus 123 anymore.
LOL
Good point.