Date & time
How to Use DATEDIF in Excel (Age and Tenure, with Examples)
DATEDIF calculates the difference between two dates and returns it as a whole number of years, months, or days. It is the function you reach for to work out someone's age, how long an employee has worked, or how many months are left on a contract.
DATEDIF is a hidden function: Excel does not show it in the formula tooltip and will not autocomplete it, but it still works when you type it in full. This guide covers the exact syntax, the unit codes, real worked examples, and the quirks that catch people out.
DATEDIF syntax
=DATEDIF(start_date, end_date, unit) | Argument | Required | What it does |
|---|---|---|
start_date | Required | The earlier date. Must be on or before end_date, or the formula returns #NUM!. |
end_date | Required | The later date. Use TODAY() to measure up to the current day. |
unit | Required | A text code in quotes that sets what to return: "Y" whole years, "M" whole months, "D" days. |
unit (combined) | Optional | "YM" months ignoring years, "MD" days ignoring months and years, "YD" days ignoring years. |
Note: Excel will not autocomplete DATEDIF, so type the whole name. It also exists in Google Sheets with the same arguments.
Examples
Real, copy-paste-ready formulas.
1. Age in completed years
=DATEDIF(B2, TODAY(), "Y") Returns the number of full years between the birth date and today. "Y" counts only completed years, so it matches how age is normally stated.
2. Tenure in years, months, and days
=DATEDIF(C2, TODAY(), "Y")&" years, "&DATEDIF(C2, TODAY(), "YM")&" months, "&DATEDIF(C2, TODAY(), "MD")&" days" Combines three DATEDIF calls: "Y" for full years, "YM" for the leftover months, and "MD" for the leftover days, joined into one sentence like "4 years, 7 months, 12 days".
3. Months on a contract
=DATEDIF(A2, B2, "M") Returns the number of complete months between the two dates. A partial month at the end is not counted until the day-of-month is reached.
4. Days until a deadline
=DATEDIF(TODAY(), D2, "D") With "D", DATEDIF returns the plain number of days. For a simple day count you can also just subtract: =D2-TODAY().
How to write DATEDIF step by step
- 1
Click the cell where you want the result and type =DATEDIF(
- 2
Click the earlier date (start_date), then type a comma.
- 3
Click the later date or type TODAY() for end_date, then a comma.
- 4
Type the unit code in quotes: "Y" for years, "M" for months, or "D" for days.
- 5
Close the bracket ) and press Enter. Format the cell as a number if it shows as a date.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#NUM! | The start_date is later than the end_date, so the difference is negative. | Put the earlier date first. If dates can arrive in either order, wrap them with MIN() and MAX(). |
#VALUE! | A date argument is actually text, or the unit code is misspelled or missing its quotes. | Make sure both dates are real dates (right-aligned by default) and the unit is in quotes, such as "Y". |
#NAME? | DATEDIF is mistyped, since Excel does not autocomplete it. | Type the function name exactly as DATEDIF with no spaces. |
Wrong "MD" result | The "MD" unit can return a negative or off-by-one day count across certain month boundaries. | For exact day-level accuracy, prefer subtracting dates, or compute the breakdown with EOMONTH-based math. |
Frequently asked questions
Why does Excel not show DATEDIF in the function list?
DATEDIF is a legacy compatibility function kept for spreadsheets that need it. Excel does not advertise it, so there is no tooltip or autocomplete, but it still calculates correctly when you type the full name.
What are the DATEDIF unit codes?
"Y" returns whole years, "M" whole months, and "D" days. The combined codes "YM", "MD", and "YD" return the remainder after the larger units: months ignoring years, days ignoring months and years, and days ignoring years.
How do I calculate exact age with DATEDIF?
Use =DATEDIF(birthdate, TODAY(), "Y") for full years. To show years, months, and days, concatenate three calls using "Y", "YM", and "MD".
Does DATEDIF work in Google Sheets?
Yes. Google Sheets supports DATEDIF with the same start_date, end_date, and unit arguments, and the same unit codes.