BeginnerDate & Time
Count complete years of service with DATEDIF
Turn a start date and a review date into whole years, the way HR counts them.
DATEDIF
5 min15 points
DATEDIF
Counts the complete years, months or days between two dates.
Syntax
DATEDIF(start_date, end_date, "Y" | "M" | "D")
Date & Time.
Practise DATEDIF1 interactive exercise, free, no account needed
A worked example
=DATEDIF(B2,E2,"Y")
Returns 7
Counts the complete years between a start date and a review date — a day short of the eighth anniversary is still 7.
Where it goes wrong
It is missing from Excel's function list and will not autocomplete, so people assume it was removed. It works in every version; type it in full.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- That gives 7.58 — a fraction of a year, and one that drifts every leap year. Long service is counted in complete years, which means asking how many anniversaries have passed, not dividing days.Count complete years of service with DATEDIF
- Seven is right for Dana this year, and wrong for her next year and for everyone else now. The number has to come from the two dates so it updates when the review date does.Count complete years of service with DATEDIF
Practise DATEDIF
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All DATEDIF practice exercises →