The DATEDIF function in Excel
DATEDIF works out how much time lies between two dates, in complete years, months or days.
What DATEDIF does
You give it a start date, an end date and a unit in quotes. The unit "Y" counts complete years, "M" complete months and "D" days. "YM" gives the months left over after the whole years are taken away, which is handy for showing a length of service as years and months. A unit only counts once it has fully passed, so two years and eleven months is still 2 years.
Syntax
=DATEDIF(start_date, end_date, unit)
- start_date
- The earlier date.
- end_date
- The later date. It must not be before the start date.
- unit
- In quotes: "Y" years, "M" months, "D" days, "YM" months after whole years.
Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Start date | Review date | Full years |
| 2 | Ana | 12/05/2018 | 20/03/2024 | 5 |
| 3 | Ben | 01/09/2020 | 14/08/2024 | 3 |
| 4 | Cy | 30/11/2019 | 01/12/2023 | 4 |
| 5 | Di | 15/01/2022 | 15/01/2024 | 2 |
Ana started in May 2018 and the review is in March 2024, which is not yet six full years, so the result is 5.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
HR
Work out length of service and eligibility dates.
Finance
Count the months a contract or invoice has been open.
Common mistakes
- Putting the dates the wrong way round. DATEDIF returns an error if the start date is after the end date.
- Leaving out the quotes around the unit. "Y" is text and needs quotation marks.
- Expecting rounding. DATEDIF counts complete units only, so it always rounds down.
The DATEDIF course
- 1Complete years between datesFree practice belowPractise
- 2Complete monthsMembers lesson
- 3Days between datesMembers lesson
- 4Years and months togetherMembers lesson
- 5Eligible after a set timeMembers lesson
Common questions
Why is DATEDIF not in the function list?
Excel does not show it in the function wizard, but it works when you type it.
How do I show years and months together?
Join two results: =DATEDIF(B2,C2,"Y")&" years "&DATEDIF(B2,C2,"YM")&" months".
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.