The Excel DATEDIF function measures the gap between two dates in whole years, months, or days — it is the function for calculating someone’s age. It has been in every version of Excel since the 90s, yet Microsoft removed it from the documentation and IntelliSense, so you have to type it from memory. It still works perfectly; you just have to know the secret handshake. Pair it with TODAY for ages and tenures that update themselves.
"Y" whole years, "M" whole months, "D" days. Excel will not autocomplete DATEDIF — type the whole thing and it works anyway.
Syntax
| Argument | Description | |
|---|---|---|
start_date | Required | The earlier date. Must not be after end_date, or the formula fails. |
end_date | Required | The later date. |
unit | Required | A text code, in quotes: "Y" whole years · "M" whole months · "D" days · "YM" months left over after the years · "MD" days left over after the months · "YD" days ignoring the years. |
Available in: every version of Excel, desktop and web — but you will not see it in the function list or get IntelliSense help while typing. That is deliberate on Microsoft’s part (it was kept for Lotus 1-2-3 compatibility), not a sign your Excel is broken.
All six units on one date pair
With a start date of 03/15/2024 in E1 and an end date of 06/01/2026 in E2, the six unit codes return:
| A | B | C | |
|---|---|---|---|
| 1 | Unit | Formula | Result |
| 2 | "Y" | =DATEDIF($E$1,$E$2,"Y") | 2 |
| 3 | "M" | =DATEDIF($E$1,$E$2,"M") | 26 |
| 4 | "D" | =DATEDIF($E$1,$E$2,"D") | 808 |
| 5 | "YM" | =DATEDIF($E$1,$E$2,"YM") | 2 |
| 6 | "MD" | =DATEDIF($E$1,$E$2,"MD") | 17 |
| 7 | "YD" | =DATEDIF($E$1,$E$2,"YD") | 78 |
Read the bottom three as the “remainder” units: the gap is 2 whole years ("Y"), plus 2 more months ("YM"), plus 17 more days ("MD"). Chain them into a sentence with concatenation:
And the classic, the formula this function exists for — current age from a birth date:
Try it: interactive DATEDIF demo
Pick two dates and a unit code, and watch DATEDIF do its thing.
Age, tenure, and other real-world recipes
Employee tenure in years and months, from a hire date in B2:
Days until the next birthday, given a birth date in A2 — "YD" measures the gap ignoring years, so subtract it from the year length:
Whole months a subscription has been active — months are only counted once the same day-of-month is reached, which is usually exactly what billing logic wants:
Just need days? Plain subtraction =B2-A2 or the DAYS function does that without any hidden-function mystery. DATEDIF earns its keep on years and months, which subtraction cannot do.
Errors & common pitfalls
#NUM! — start date is after end date. DATEDIF refuses to count backwards. Swap the arguments, or wrap the dates in MIN and MAX if either could come first: =DATEDIF(MIN(A2,B2), MAX(A2,B2), "D").
#NUM! — bad unit code. Only the six codes listed above are accepted, and they must be text in quotes: "Y", not Y. A cell reference holding the code also works.
Pitfall: no IntelliSense is normal. You type =DATED and Excel suggests nothing — every other function autocompletes, so it feels like DATEDIF does not exist. It does. Finish typing the formula and it calculates fine. Microsoft hides it on purpose; it is a feature with a paper bag over its head, not a missing function.
Pitfall: the "MD" unit has genuine quirks. Microsoft itself documents that "MD" can return odd results — including a negative day count — for certain month-end combinations, such as a start late in January measured against early March. "Y", "M", "D", and "YM" are solid; treat "MD" output as approximate, or compute leftover days yourself with =B2 - EDATE(A2, DATEDIF(A2,B2,"M")).
Pitfall: "Y" counts completed years only. Someone turns 30 on their birthday, not the day before — DATEDIF agrees, returning 29 right up until the anniversary date. That is correct for ages, but if you want fractional years, use YEARFRAC instead.
Practice workbook
Frequently asked questions
Why doesn't DATEDIF show up in Excel's function list or autocomplete?
How do I calculate age in Excel?
=DATEDIF(birth_date, TODAY(), "Y"). It returns whole completed years and rolls over exactly on the birthday. For age as a decimal, use =YEARFRAC(birth_date, TODAY(), 1) wrapped in INT.What's the difference between the "YM", "MD", and "YD" units?
"YM" gives the months left over after whole years are removed (always 0–11); "MD" gives the days left over after whole months; "YD" gives the day gap as if both dates were in the same year. Together, "Y" + "YM" + "MD" build the classic "2 years, 2 months, 17 days" output.Why does DATEDIF return #NUM!?
Is it safe to rely on an undocumented function?
"Y", "M", "D", and "YM": yes — DATEDIF has behaved identically for roughly three decades and Microsoft acknowledges it exists. The one caveat is "MD", which Microsoft itself warns can misbehave around month ends.Can I just subtract two dates instead of using DATEDIF?
=B2-A2 gives the day count directly. But subtraction cannot return whole years or whole months — that calendar-aware counting is exactly what DATEDIF (and nothing else in classic Excel) provides.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class