“42 years, 3 months, 18 days.” DATEDIF breaks an age (or any duration) into whole years, leftover months, and leftover days — with three different unit codes doing the work.
"y" = whole years, "ym" = months past the last full year, "md" = days past the last full month.
The example
Born March 1, 1983, measured to June 17, 2026.
| A | B | |
|---|---|---|
| 1 | Birth date | 3/1/1983 |
| 2 | Years | 43 |
| 3 | + Months | 3 |
| 4 | + Days | 16 |
The formula
The three components, combined into one label:
How it works
DATEDIF returns a different slice for each unit code:
"y"gives complete years between the dates — the age in years."ym"gives the months remaining after those whole years (0–11)."md"gives the days remaining after the whole months.- Concatenate all three with
&for a human-readable string, or keep them in separate cells for sorting and math.
DATEDIF is hidden but works everywhere. Excel doesn’t show it in autocomplete or document it in the function list, but it’s supported in every version. Just type it in full.
Try it: interactive demo
Enter a birth (or start) date.
Variations
Just whole years
Age in years only:
Total months
Whole months across the whole span:
Total days
Or simply subtract:
Pitfalls & errors
"md" can misbehave. The day-only code occasionally returns odd results across month boundaries with very different month lengths. For critical calcs, verify against a manual check.
Order matters. The earlier date goes first. A start date after the end date returns a #NUM! error.
No autocomplete. Excel won’t suggest DATEDIF as you type — that’s expected. Type the whole name and the quoted unit code.
Practice workbook
Frequently asked questions
How do I calculate exact age in years, months, and days in Excel?
Why doesn't Excel show DATEDIF in autocomplete?
How do I get just the age in years?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class