The DATEDIF function calculates the period or the difference between two dates in days, months, and years. You can use the DATEDIF function to determine the time frame for an upcoming project, or it can be used, along with a person’s birth date, to calculate an individual’s age in years, months, and days, for example.

DATEDIF Function Syntax and Arguments

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:

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

=DATEDIF(start_date,end_date,“unit”)

  • start_date (required): You can input the start date of the chosen period or the cell reference to the location of this data in the worksheet.
  • end_date (required): As with the start date, you can enter the end date of the chosen period or a cell reference.
  • unit (required): The unit tells the function to find the number of days (“D”), complete months (“M”), or complete years (“Y”) between the two dates. You must surround the unit argument with quotation marks such as “D” or “M.”

In addition to D, M, and Y, there are three other unit options that you can see in the image below:

  • “YD” calculates the number of days between two dates, but ignores the years (row 5).“YM” calculates the number of months between two dates, but ignores the day and the year (row 6).“MD” calculates the number of days between two dates, but ignores the month and the year (row 7).

DATEDIF is a hidden function so you won’t find it listed with other Date functions under the Formula tab in Excel, which means you can’t use the Function Dialog Box to enter it. As a result, you have to input the function and its arguments manually into a cell.

Excel carries out date calculations by converting the dates to serial numbers. December 31, 1899, is serial number 1 and January 1, 2008, is serial number 39488 because it is 39,488 days after January 1, 1900.

Calculating the Difference in Days With DATEDIF

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.

=DATEDIF(A2,A3,“D”)

Below are the steps to input this function using cell references.

  • Click cell B2 to make it the active cell; this is where the number of days between the two dates will display.
  • In cell B2, type =datedif(.
  • Click cell A2 to enter this cell reference as the start_date argument for the function.
  • Type a comma ( , ) in cell B2 following the cell reference A2 to act as a separator between the first and second arguments.
  • Click cell A3 to enter this cell reference as the end_date argument.
  • Type a comma ( , ) following the cell reference A3.
  • 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.
  • Type a closing parenthesis.
  • Press the Enter key on the keyboard to complete the formula.
  • The number of days — 829 — appears in cell B2 of the worksheet.
  • When you click on cell B2, the complete formula appears in the formula bar above the worksheet.

DATEDIF Error Values

If you don’t enter the data for the arguments in this function correctly, the following error values appear:

Click cell B2 to make it the active cell; this is where the number of days between the two dates will display.

In cell B2, type =datedif(.

Click cell A2 to enter this cell reference as the start_date argument for the function.

Type a comma ( , ) in cell B2 following the cell reference A2 to act as a separator between the first and second arguments.

Click cell A3 to enter this cell reference as the end_date argument.

Type a comma ( , ) following the cell reference A3.

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.

Type a closing parenthesis.

Press the Enter key on the keyboard to complete the formula.

The number of days — 829 — appears in cell B2 of the worksheet.

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

  • #VALUE!: You’ll get this error if either the start_date or the end_date is not an actual date (row 8 in the image below, where the cell A8 contains text data).#NUM!: You’ll see this if the end_date is an earlier date than start_date (row 9 below).

Get the Latest Tech News Delivered Every Day