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

By Ali, Founder, Excel Lessons

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. Three things to watch. It counts complete units. So somebody 11 months into a job shows as zero years. That is correct, and it needs explaining to whoever reads the report. 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. And Microsoft says not to rely on md. On some pairs of dates it gives a negative number or zero. Use it for a rough remainder, and check it where that is not enough.

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.

Read more about this

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

DATEDIF counts complete years, months or days between two dates.

In D2, type:

=DATEDIF(B2,B6,"y")

B2 is the start date, B6 the review date.

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

    DATEDIF counts complete years, months or days between two dates. In D2, type: =DATEDIF(B2,B6,"y"). B2 is the start date, B6 the review date.

    Hint. The unit goes in quotes.

  2. 2

    In E2, use DATEDIF for whole months.

    Hint. Only one letter in the unit changes.

  3. 3

    ym and md give the leftover months and days. Read =DATEDIF(B3,B6,"ym") and say what it returns.

    Hint. Whole years first, then what is left.

  4. 4

    In D4, use DATEDIF to find B4's complete years to B6.

    Hint. The unit is a single letter in quotes.

  5. 5

    D3 should give complete years but returns an error. Fix D3.

    Hint. Which date came first?