How to calculate age or years of service in Excel
Subtracting two dates gives days. Dividing by 365 is close, but leap years make it slightly wrong on anniversaries.
DATEDIF counts complete years, months or days between two dates exactly.
The formula
| A | B | |
|---|---|---|
| 1 | Start date | Today |
| 2 | 2019-03-15 | 2026-11-07 |
=DATEDIF(A2,B2,"y")Result: 7
Seven complete years have passed since March 2019.
Step by step
- Type =DATEDIF( and click the earlier date.
- Add a comma and click the later date.
- Add ,"y" for years, ,"m" for months or ,"d" for days, then close the bracket.
Other ways to write it
=YEAR(B2)-YEAR(A2)-(TEXT(B2,"mmdd")<TEXT(A2,"mmdd"))Works it out from the years, minus one if this year’s anniversary hasn’t come yet.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Dates in the wrong order
- DATEDIF gives #NUM! if the start date is after the end date.
- Using TODAY() in a report
- TODAY() changes every day, so a saved report gives a different answer tomorrow. Use a fixed date when that matters.
Reading isn’t the same as doing
Tenure and working days gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.