Using the DATEDIF Function in Excel to Count Days, Months, and Years

Calculate the Difference Between Two Dates in Excel

Top view of woman holding smartphone and tablet with calendar on desk

 Getty Images/Westend61

Excel has several built-in date functions that can be used to calculate the number of days between two dates — each date function does a different job, so the results differ. The DATEDIF function can be used to calculate the period or the difference between two dates in days, months, and years.

Uses for the DATEDIF function can include planning or writing proposals to determine the time frame for an upcoming project; it can also be used, along with a person's birth date, to calculate an individual's age in years, months, and days.

DATEDIF Function Syntax and Arguments

Screenshot of Excel showing the DATEDIF Function syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the DATEDIF function is:

= DATEDIF(start_date, end_date, unit)
  • start_date (required): The start date of the chosen period can be entered for this argument or the cell reference to the location of this data in the worksheet can be entered instead.
  • end_date (required): The end-date of the chosen period, as with the start_date, can be entered as an actual end date or the cell reference to the location of this data in the worksheet.
  • unit (required): Unit (formerly called interval) tells the function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates. The argument must be surrounded by quotation marks such as "D" or "M."

Excel carries out date calculations by converting the dates to serial numbers that begin at zero for the fictitious date January 0, 1900, on Windows computers and January 1, 1904, on Macintosh computers.

More About The Unit Argument

  • "YD" calculates the number of days between two dates as if the dates are in the same year (row 5).
  • "YM" calculates the number of months between two dates as if the dates are in the same year (row 6).
  • "MD" calculates the number of days between two dates as if the dates are in the same month and year (row 7).

Calculating the Difference in Days with DATEDIF

Screenshot of Excel showing different DATEDIF Functions

Here's how to enter the DATEDIF function located in cell B2, as shown in the example image above, to display the number of days between the dates May 4, 2014, and August 10, 2016.

  1. Click on cell B2 to make it the active cell; this is where the number of days between the two dates will be displayed.
  2. Type = datedif( into cell B2.
  3. Click on cell A2 to enter this cell reference as the start_date argument for the function.
  4. Type a comma in cell B2 following the cell reference A2 to act as a separator between the first and second arguments.
  5. Click on cell A3 in the spreadsheet to enter this cell reference as the end_date argument.
  6. Type a comma following the cell reference A3.
  7. For the unit argument, type the letter D in quotes ("D" ) to tell the function to display the number of days between the two dates.
  8. Type a closing parenthesis.
  9. Press the Enter key on the keyboard to complete the formula.
  10. The number of days — 829 — appears in cell B2 of the worksheet.
=DATEDIF(A2,A3,"D")

When you click on cell B2, the complete formula appears in the formula bar above the worksheet.

DATEDIF Error Values

Screenshot of Excel showing the DATEDIF Function error values

If the data for the various arguments of this function are not entered correctly, the following error values appear in the cell where the DATEDIF function is located:

  • #VALUE!: The error is returned if either the start_date or the end_date is not an actual date (row 8 in the image, where the cell A8 contains text data).
  • #NUM!: The error is returned if end_date is an earlier date than start_date (row 9).

DATEDIF Function Is Hidden

Datedif is a hidden function that is not listed with other Date functions under the formula tab in Excel, which means:

  • No Formula Builder is available for entering the function and its arguments.
  • The argument tooltip doesn't display the argument list when the function's name is typed into a cell.

As a result, the function and its arguments must be entered manually into a cell for it to be used, including typing a comma between each argument to act as a separator.