Show length of service precisely — “5 years, 3 months, 12 days” — from a hire date to today (or a termination date). DATEDIF with three unit codes builds each piece.
The example
Hired March 7, 2021.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Hire date | 2021-03-07 |
| 3 | Tenure | 5 yr 3 mo 12 d |
The formula
The formula:
How it works
How it works:
DATEDIF(hire, end, "y")returns the number of complete years."ym"gives the leftover whole months after those years."md"gives the leftover days after the months.- Concatenate the three with labels for a readable tenure string; use a termination date in place of TODAY() for former staff.
DATEDIF is hidden but supported. It won’t appear in Excel’s function autocomplete — type it in full. The "md" unit can occasionally misbehave around month boundaries; for total years as a decimal, YEARFRAC(hire, end) is a robust alternative.
Try it: interactive demo
Hire date.
Variations
Whole years only
For service awards:
Total months
All months:
Decimal years
Robust alternative:
Pitfalls & errors
Not in autocomplete. DATEDIF is undocumented — type the whole name yourself.
Order matters. The earlier date (hire) comes first; a later start than end errors.
"md" quirks. The day component can be off near month ends; prefer YEARFRAC for precise decimals.
Practice workbook
Frequently asked questions
How do I calculate employee tenure in Excel?
How do I get whole years of service only?
Why can't I find DATEDIF in Excel?
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