DATEDIF in Excel: Years, Months and Days Between Dates
Use DATEDIF to calculate complete years, months or days between two dates, ages and lengths of service, with all unit codes explained and known quirks.
By Ayoub Farid · · 1 min read
DATEDIF counts the complete years, months or days between two dates. It's the standard way to calculate ages and length of service. Oddly, it doesn't appear in Excel's function autocomplete, but it works in every version and in Google Sheets.
Syntax
=DATEDIF(start_date, end_date, unit)| Unit | Returns |
|---|---|
| "y" | Complete years |
| "m" | Complete months |
| "d" | Days |
| "ym" | Months left after whole years |
| "md" | Days left after whole months |
| "yd" | Days left after whole years |
Age
=DATEDIF(B2,TODAY(),"y")Years and months of service
=DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months"→ "4 years, 7 months"
Start must come before end
If start_date is after end_date, DATEDIF returns #NUM!. Wrap with IFERROR, or check your columns.
The "md" quirk
Microsoft documents that the "md" unit can give inaccurate results in some cases. If exact leftover days matter (legal, payroll), calculate them another way, e.g. =end-EDATE(start,DATEDIF(start,end,"m")).
Just days? Subtract
For total days you don't need DATEDIF:
=C2-B2For working days, use NETWORKDAYS.
Frequently asked questions
Why can't I find DATEDIF in Excel?
It's a legacy function kept for compatibility, so it isn't listed in autocomplete. Type it fully and it works.
How do I calculate age in Excel?
=DATEDIF(birthdate,TODAY(),"y"). See calculate age.
Why does DATEDIF return #NUM!?
The start date is later than the end date.
Does DATEDIF work in Google Sheets?
Yes, with the same units.