Excel DATEDIF Function: Units, Errors and Limits
DATEDIF answers one question in six different ways. The arguments are always the same two dates, but the third argument — the unit — decides whether you get years, months, days, or a remainder of one inside the other.
Because the function is not in Excel's function list, the units are easy to misremember and the errors it returns are unusually opaque. This page is the reference for both.
Syntax and the Unit Table
The syntax is DATEDIF(start_date, end_date, unit), and the unit is a quoted string:
| Unit | Returns | Use it for |
|---|---|---|
"Y" | Completed years | Age, service length, anniversaries |
"M" | Completed months | Total months since a start date |
"D" | Elapsed days | Plain day counts; identical to subtracting the two dates |
"YM" | Months 0–11 | The months figure in a years-and-months age |
"MD" | Days 0–30 | The days figure in a years-months-days age |
"YD" | Days 0–365 | Days since the last anniversary |
Only the first three are cumulative. The last three are remainders: they deliberately discard the larger units so the answer fits neatly into a readable age.
Worked Example: Every Unit on the Same Two Dates
From 1990-06-15 to 2026-09-20, the six units return:
| Formula | Result |
|---|---|
=DATEDIF(A2,B2,"Y") | 36 |
=DATEDIF(A2,B2,"M") | 435 |
=DATEDIF(A2,B2,"D") | 13,246 |
=DATEDIF(A2,B2,"YM") | 3 |
=DATEDIF(A2,B2,"MD") | 5 |
=DATEDIF(A2,B2,"YD") | 97 |
Read the last three as a decomposition of the first: 36 years, plus 3 months, plus 5 days. And check the arithmetic against the cumulative units — 36 × 12 + 3 = 435, which is exactly what the "M" unit reported. When a manual addition like that does not match, you have found a wrong argument rather than a broken function.
The "YD" figure of 97 days is the distance from 15 June to 20 September, ignoring the years entirely — useful for "how long until their birthday" type questions.
Two Errors You Will Actually Hit
#NUM! appears when the start date is later than the end date. DATEDIF has no concept of a negative interval, so it refuses rather than reversing the answer. The fix is to swap the references, or to guard the formula with IF(A2>B2, DATEDIF(B2,A2,"Y"), DATEDIF(A2,B2,"Y")) if the column really can contain dates in either order.
Negative or odd values from the "MD" unit are the other classic. Because "MD" works on day numbers while ignoring month lengths, some month pairs leave it reporting a value that does not fit the 0 to 30 range you would expect. Microsoft's own documentation warns that the function may calculate incorrect results in certain scenarios. If the days figure looks impossible, compute the total elapsed days with "D" and derive the remainder yourself.
Why the Function Is Hidden
Excel inherited DATEDIF from Lotus 1-2-3. Microsoft kept it so that old spreadsheets would keep working, but never promoted it into the function list. The practical consequences are small but genuinely annoying: no autocomplete, no argument tooltip, and no error highlighting if you get the unit wrong.
A mistyped unit is the quiet failure mode. DATEDIF(A2,B2,"y") works — units are case-insensitive — but DATEDIF(A2,B2,"YY") returns an error, and writing "YM" where you meant "M" returns a perfectly valid number that is simply the wrong one. Nothing in the sheet will flag it.
Portability: Sheets and LibreOffice
Google Sheets documents DATEDIF in its own help pages, so the function is not a legacy secret there. LibreOffice Calc also supports it. The same six units work in all three applications, which makes it one of the better choices for a formula that has to survive being pasted between them.
The portability caveat is not the function but the underlying date numbering: Sheets and Excel count days from different starting points, which matters when raw serial numbers are copied rather than dates. That difference is covered in the Google Sheets age formula guide.
The Misconception: DATEDIF Counts Anniversaries, Not Calendar Years
A frequent misunderstanding is that "Y" behaves like subtracting one year from another. It does not. DATEDIF counts completed intervals, so the answer increments on the anniversary date and at no other time.
This is a feature, not a quirk. It means a person born on 1 December is not suddenly a year older on 1 January, which is precisely the error that the =YEAR(TODAY())-YEAR(A2) shortcut introduces for several months of every year.
Edge Cases: Leap Days and Month Ends
A 29 February birth date is the classic hard case, because in three years out of four there is no corresponding anniversary day. Different tools resolve it differently — some roll forward to 1 March, some back to 28 February — so for a leap-day birth date, check the result by hand before publishing it anywhere.
Month ends create a milder version of the same problem. The distance from 31 January to 1 March is 29 days in a common year and 30 in a leap year, and a formula that assumes one fixed month length will be wrong about half the time. DATEDIF handles both correctly because it works on real calendar dates rather than on an average month.
Counting Days Without DATEDIF
For a plain day count you do not need the function at all:
=B2-A2This returns exactly what DATEDIF(A2,B2,"D") returns, and it is easier for a colleague to audit because there is no hidden unit argument to misread. The one difference is formatting: subtraction inherits the date format of its inputs and may display a date instead of a number, which is the trap described in the days between two dates guide.
Measure any span in days, weeks and months without touching a formula.
Open the Date Duration Calculator →Frequently Asked Questions
Is DATEDIF still supported in current versions of Excel?
Yes. It works in Excel for Windows, Excel for Mac, Excel on the web, LibreOffice Calc and Google Sheets. Microsoft keeps it for backwards compatibility with older workbooks.
Why does Excel not autocomplete DATEDIF?
Because the function is retained for compatibility rather than documented in the function list. There is no argument tooltip, so you have to type the name and all three arguments yourself.
What is the difference between the M unit and the YM unit?
The M unit counts all completed months from the start date, so it grows past 12. The YM unit counts only the months left after whole years are removed, so it always stays between 0 and 11.
Can DATEDIF return a negative number?
Not for the Y and M units, which return #NUM! instead. The MD unit is the exception and can return values outside its expected range in some month combinations.
How do I stop the #NUM! error?
Make sure the earlier date is the first argument. If the column can hold dates in either order, wrap the whole call in an IF test that swaps the arguments when needed.
Does the D unit include the start date?
No. It measures elapsed days, so 1 September to 5 September is 4. Add 1 to the result if you want both end days counted as full days.
Do the units work the same way in Google Sheets?
Yes, all six units behave the same. Sheets documents the function rather than hiding it, but the results match for ordinary dates.
What happens with a 29 February birth date?
There is no matching anniversary in three years out of four, and different tools choose different fallback days. For leap-day dates, verify the result by hand rather than trusting the formula.
Is there a faster way to get an age in whole years?
INT with YEARFRAC using basis 1 gives the same answer and avoids the legacy function, at the cost of a less obvious formula.
Are the dates I enter on this site kept private?
Yes. Everything runs in your browser — nothing is sent to a server and nothing is saved.