Excel's Secret DATEDIF Function

How to Calculate Age and Other Time Periods with Excel’s Secret DATEDIF Function

By Marie McCooey

How can you calculate the number of days, months or years between two dates?

For example:

  • Your age in days, months and years
  • An employee’s length of service
  • Length of a project

Let me introduce you to DATEDIF (i.e. Date difference), a very useful, but hidden function used to calculate the difference between two dates in the number of days, months or years.

The History of the DATEDIF function

The DATEDIF (DATE + DIF) function was initially provided to support older workbooks from Lotus 1-2-3.

If you started as a LOTUS user and remember this handy function, you’ll be happy to know it’s still available. Although it has not been documented since the Excel 2000 version, you can use it in any Excel version.

However, without any documentation, it has been a challenge to use this valuable function.

Note: If you have the 2016 version of Excel, you’ll be able to use the new ‘Tell me what you want to do’ feature on the Ribbon to find information on the function.

Tell me what you want to do

Click the ‘Tell me what you want to do’ feature and the following two options display.

Tell me what you want to do options

Select Get Help on DATEDIF, then select one of the descriptions of the DATEDIF options.
A Help window displays DATEDIF information on the right side of the screen.

For anyone who uses a pre-2016 Excel version read on to discover how to use DATEDIF.

Why is the DATEDIF function a secret?

There is a bit of a mystery around DATEDIF.

You will not find DATEDIF in the Function Library on the Formulas tab, the Insert Function feature or in the list of suggested functions when you start typing the function name.

You also won’t get any hints on which arguments to enter when you start typing the function’s name in the formula bar.

That is why it’s important to know the complete syntax of DATEDIF to be able to use it in your formulas.

DATEDIF Syntax

The Excel DATEDIF function = DATEDIF(start_date, end_date, unit) requires the following three arguments:

Start_date – The date from where you want to start the calculation of your interval.

End_date – The ending date to which you want to calculate the interval.

The Start date and End date are the two dates to calculate the difference between.

Dates can be input as:

  • Cell references
    Example: The following formula calculates the number of complete years between the dates in cells A2 and B2 where A2 = 9/24/1992 and B2 = 7/8/2017:

=DATEDIF(A2, B2, “y”) = 24 (years)

  • Text strings
    Example: The following formula calculates the number of complete months between the specified dates:

=DATEDIF(“2/10/2017”, “7/20/2017”, “m”) = 4 (months)

  • Results of other day functions, e.g. the TODAY and NOW functions.
    Example: The following formula counts how many days there are between today’s date and July 8, 2017.

=DATEDIF(TODAY(), “7/8/2017”, “d”) = 127 (days) where TODAY = 3/3/17

Unit – The type of interval to be displayed, e.g. years, months, or days.

Unit Description
Y (Years) The number of complete calendar years between two dates
Note: Excess Months and Days are ignored
M (Months) The number of complete calendar months between two dates
Note: Excess Days are ignored
D (Days) The number of complete days between two dates
YD The number of days between the two dates as though the End date is in the same year as the Start date.
Note: The years of the dates are ignored.
YM Complete calendar months between the two dates as though the End date is in the same year as the Start date.
Note: The days and years of the dates are ignored.
MD Complete calendar months between two dates as though the end date is in the same year as the start date.
The difference between the days in start_date and end_date.
Note: The months and years of the dates are ignored.

DATEDIF Function Examples

DATEDIF Examples

DATEDIF Limitations

  • The Start Date must be less than the End Date.
  • The Start Date and End Dates must be valid dates.
  • Excel cannot calculate dates before January 1, 1900 on Windows PCs, and January 1, 1904 on Apple Mac systems.
  • The DATEDIF() function always rounds down (by default) to the nearest whole month or year.