Home › Excel functions › DATEDIF
DATEDIF in Excel: Calculate Age and Time Between Dates
DATEDIF calculates the difference between two dates in complete years, months or days. It's the standard way to calculate someone's age or years of service, even though Excel doesn't list it in the function wizard.
DATEDIF · In this guide
DATEDIF syntax
=DATEDIF(start_date, end_date, unit)| Unit | Returns |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Days |
"YM" | Months left over after the complete years |
"MD" | Days left over after the complete months (see quirks below) |
"YD" | Days left over after the complete years |
Hidden function: DATEDIF doesn't appear in autocomplete or the Insert Function dialog. Type it in full; it works in every Excel version.
Calculate age
| A | B | C | |
|---|---|---|---|
| 1 | Name | Date of birth | Age on Nov 12, 2026 |
| 2 | Anna | May 20, 1990 | 36 |
| 3 | Ben | Dec 1, 1985 | 40 |
Only complete years count: Ben turns 41 on December 1, so on November 12 he's still 40.
More variations (age in years and months, on a specific date, age groups, birthdays) are in our age formula guide.
"36 years, 5 months, 23 days"
Combine the units to show the full difference. From May 20, 1990 to November 12, 2026:
=DATEDIF(B2, C1, "Y") & " years, " & DATEDIF(B2, C1, "YM") & " months, " & DATEDIF(B2, C1, "MD") & " days"returns 36 years, 5 months, 23 days. Handy for years of service or contract duration.
Just the number of days
For days you don't need DATEDIF: simply subtract, =C2-B2. For working days excluding weekends, use NETWORKDAYS.
Quirks and errors
- #NUM!: the start date is after the end date. Swap them.
- "MD" can return odd results (even negative numbers) near the end of a month. Microsoft itself advises against relying on it. For exact day remainders, calculate:
=C1-EDATE(B2, DATEDIF(B2, C1, "M")). - #VALUE!: one of the dates is text, not a real date. See DATE.
- Fractional years: for decimal years (such as 36.48), use
=YEARFRAC(B2, C1)instead.
Frequently asked questions
Why can't I find DATEDIF in Excel?
It's a hidden compatibility function. It works, but doesn't show up in autocomplete. Type the whole formula yourself.
How do I calculate age in Excel?
Use =DATEDIF(date_of_birth, TODAY(), "Y").
How do I calculate months between two dates?
Use =DATEDIF(start, end, "M") for complete months.