Excel Practice
Lessons Lesson 78

DATEDIF: The Undocumented One

About this lesson

DATEDIF returns the number of complete years, months or days between two dates. The unit you ask for decides which.

By the end you can

  • count whole years, months or days between two dates with DATEDIF.
  • use the remainder units to say 4 years and 9 months.

Practises DATEDIF TODAY YEAR

The idea

Three arguments: the earlier date, the later date, and a unit in quotation marks. Excel does not offer it in the function list and has never documented it properly. It survives from Lotus 1-2-3. But it works everywhere, and there is no modern replacement.

It earns its place on the leftover units. Anyone can get a rough number of years by subtracting and dividing by 365.25, and it is wrong near birthdays. What is hard by hand is "5 years and 2 months". ym gives the months remaining after the whole years. md gives the days remaining after the whole months. Two things to watch. It counts complete units. So somebody eleven months into a job shows as zero years. That is correct, and it needs explaining to whoever reads the report. And it refuses if the end date is before the start. That is a good thing. A swapped pair gives an error instead of a negative length of service that nobody notices.

The mistake to watch for

Putting the later date first. DATEDIF refuses with an error instead of returning a negative. That is a kindness once you know it. The quieter trap is expecting a part-year to count. Somebody eleven months into a job shows as zero years. That is correct, and it needs explaining to whoever reads the report.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

How many complete years has the first employee served, up to the review date in B6? Put it in D2.

To begin, type it exactly:

=DATEDIF(B2,B6,"y")

D2
Row A B C D E F G
1 Employee Started Today Years Months
2 M Achebe 42005
3 P Sandoval 43831
4 K Byrne 45108
5
6 Review date 45658
7
8

Every step

  1. 1

    How many complete years has the first employee served, up to the review date in B6? Put it in D2. To begin, type it exactly: =DATEDIF(B2,B6,"y").

    Hint. The unit goes in quotes.

  2. 2

    Now in whole months, in E2.

    Hint. One letter different.

  3. 3

    The leftovers have their own units. Read =DATEDIF(B3,B6,"ym") and say how many months there are beyond the whole years for the second employee.

    Hint. Whole years first, then what is left.

  4. 4

    In D4, use DATEDIF to give the third employee's complete years of service at the review date.

    Hint. The unit is a single letter in quotes.

  5. 5

    D3 is meant to give the second employee's complete years and it is returning an error. Repair it.

    Hint. Which date came first?