Dates function
Excel DATEDIF function
DATEDIF counts the complete years, months, or days between two dates. It is the go-to way to work out someone's age or years of service. Excel does not show it in autocomplete, but it works when you type it.
=DATEDIF(start_date, end_date, unit)Arguments
start_date- The earlier date.
end_date- The later date. It must be on or after the start date.
unit- "Y" for complete years, "M" for complete months, "D" for days.
DATEDIF examples
Every result below was calculated live from this sample sheet.
| A | B | C | |
|---|---|---|---|
| 1 | Member | Joined | Renewal |
| 2 | Grace Lin | 2019-08-20 | 2026-03-01 |
| 3 | Omar Haddad | 2023-11-05 | 2026-05-04 |
| 4 | Ines Duarte | 2025-12-15 | 2026-06-15 |
Result: 6
Grace had 6 complete years of membership at renewal.
Result: 29
Omar had 29 complete months. The last month was one day short.
Result: 182
Ines had 182 days between joining and renewal.
Result: today’s date-based value
Complete years from joining until today, like an age formula.
Result: #NUM!
If the start date is later than the end date you get #NUM!.
How to use DATEDIF
- Click the cell for the result.
- Type =DATEDIF( and click the start date.
- Type a comma and click the end date.
- Add the unit in quotes, like "Y", and press Enter.
Common DATEDIF mistakes
Dates in the wrong order
The start date must come first and be the earlier date, or DATEDIF returns #NUM!.
Forgetting the quotes around the unit
Type "Y", "M" or "D" with quotes. Without them Excel returns #NAME?.
Thinking the function is missing
DATEDIF does not appear in autocomplete or the function list. Type it in full and it still works.
DATEDIF FAQ
How do I calculate age in Excel from a date of birth?
Use =DATEDIF(B2,TODAY(),"Y"). It returns completed years, so the age only goes up on the birthday.
Why can't I find DATEDIF in Excel?
It is an older compatibility function that Excel keeps but does not list. Type =DATEDIF( by hand and it works.
How do I calculate years of service in Excel?
Use =DATEDIF(hire_date,TODAY(),"Y") for whole years, or "M" for whole months.
Practice DATEDIF in these lessons
Guides that use DATEDIF
- How to calculate age in Excel from date of birth
- How to calculate days between two dates in Excel
- How to calculate months between two dates in Excel
- Excel formulas cheat sheet: the 25 everyone should know
Related functions
Updated 2026-10-31