DATEDIF returns the difference between two dates as a whole number of years, months, or days. It is the standard way to calculate an age, a length of service, or the time until a deadline. The function is unusual: it works in every version of Excel but does not appear in the function list and has no tooltip, so you have to type it from memory.
Syntax
=DATEDIF(start_date, end_date, unit)
- start_date - the earlier date.
- end_date - the later date. It must be on or after the start date, or you get a #NUM! error.
- unit - a text code, in quotes, for what to return (see the table).
Unit Codes
| Unit | Returns |
| "Y" | Complete years between the dates |
| "M" | Complete months between the dates |
| "D" | Days between the dates |
| "YM" | Months, ignoring years (0-11) |
| "MD" | Days, ignoring months and years |
| "YD" | Days, ignoring years |
Calculate Age in Years
With a birth date in A2:
=DATEDIF(A2, TODAY(), "Y")
TODAY() supplies the current date, so the age updates every day the file is opened.
Age as Years and Months
Combine two DATEDIF calls with text:
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"
The "YM" unit gives the leftover months after the whole years, so you get a clean "34 years, 5 months".
Length of Service in Months
With a hire date in A2 and an end date in B2:
=DATEDIF(A2, B2, "M")
Common Errors
- #NUM! - the start date is later than the end date. DATEDIF does not accept a negative span; swap the arguments or check the cell.
- #VALUE! - one of the arguments is not a real date. Text that only looks like a date will fail. Confirm both cells are true dates (they right-align by default).
- The "MD" unit can be unreliable across month boundaries and Microsoft advises caution with it. For precise day counts prefer plain subtraction (=B2-A2).
Frequently Asked Questions
Why is DATEDIF not showing up when I type it?
DATEDIF is a legacy function kept for compatibility with older spreadsheets. Microsoft left it out of the function list and gives it no autocomplete or tooltip, but it still calculates correctly. Type the whole formula yourself.
How do I calculate someone's age in Excel?
Use =DATEDIF(birthdate, TODAY(), "Y"). It returns the number of complete years, which is the person's age, and refreshes each day because TODAY() is always the current date.
What is the difference between "M" and "YM"?
"M" returns the total complete months between the dates, which can be a large number like 41. "YM" returns only the months left over after the whole years, always 0 to 11, which is what you want when showing "years and months".Why does DATEDIF return a #NUM! error?
The start date is after the end date. DATEDIF requires the first date to be the earlier one. Check that the cells are in the right order and that neither is a mistyped date.
Can I calculate the number of days between two dates without DATEDIF?
Yes. Subtract the earlier date from the later one: =B2-A2 returns the number of days directly, and unlike the "MD" unit it is always reliable.