The Formula Gym
XP 0
Streak 0

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

ABCD
1EmployeeStart dateReview dateFull years
2Ana12/05/201820/03/20245
3Ben01/09/202014/08/20243
4Cy30/11/201901/12/20234
5Di15/01/202215/01/20242
D2 =DATEDIF(B2,C2,"Y") (copied down 4 rows)

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

  1. 1
    Complete years between datesFree practice below
    Practise
  2. 2
    Complete monthsMembers lesson
  3. 3
    Days between datesMembers lesson
  4. 4
    Years and months togetherMembers lesson
  5. 5
    Eligible 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.

See membership