DATEDIF
Counts how many full years, months or days have passed between two dates: length of service, age, contract term.
DATEDIF(start_date, end_date, unit)- start_date
- The earlier date, for example the hire date.
- end_date
- The later date: the calculation date, or TODAY() to count as of today.
- unit
- What to count in: "y" gives years, "m" months, "d" days. Written in quotes.
Return value
A number: the difference between the dates in the units from the third argument: full years, months or days. If the start date is later than the end date, you get #NUM!.
Examples
Length of service in full years
fx
A
B
C
D
E
1
2
3
4
5
6
7
Employee
Hire date
As of
Years of service
Anderson
15.03.2019
01.10.2026
Baker
01.11.2020
01.10.2026
Collins
10.06.2024
01.10.2026
Dixon
20.10.2025
01.10.2026
Sheet1
Details
DATEDIF compares two dates and returns a whole number in the units set by the third argument. Partial years and months are dropped: 5 years and 11 months give 5 years.
The unit is a letter in quotes: "y" counts full years, "m" full months, "d" days.
Excel doesn't suggest this function as you type, but it works. You'll have to type the name and arguments in full.
Common mistakes
- The start date is later than the end date: the function returns #NUM!.
- The unit is written without quotes, for example =DATEDIF(B2,C2,y). You need "y", "m" or "d" in quotes.
- Expecting fractional service like 5.9. DATEDIF returns only full years.