Using DATEDIF to Count Days, Months, or Years in Excel

Calculate the time period or the difference between two dates

screenshot/Microsoft

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 that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.

The DATEDIF function can be used to calculate the time period or the difference between two dates. This time period can be calculated in:

  • days 
  • whole months 
  • whole years 

Uses for this function 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 his or her age in years, months, and days.

The DATEDIF Function's Syntax and Arguments

Count the Number of Days, Months, or Years between Two Dates in Excel with the DATEDIF Function
Count the Number of Days, Months, or Years between Two Dates in Excel with the DATEDIF Function. © Ted French

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 time period. The actual start date 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 time period. As with the Start_date, enter the actual end date or the cell reference to the location of this data in the worksheet.

unit (formerly called interval) – (required) tells the function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates.

Notes:

  1. Excel carries out date calculations by converting the dates to serial numbers, which begin at zero for the fictitious date January 0, 1900 on Windows computers and January 1, 1904 on Macintosh computers.
  2. The unit argument must be surrounded by quotation marks such as "D" or "M".

More on the Unit Argument

The unit argument can also contain a combination of days, months, and years in order to find the number of months between two dates in the same year or the number of days between two dates in the same month.

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

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! error – returned if either start_date or end_date are not actual dates (row 8 above, where the cell A8 contains text data).
  • #NUM! error – returned if  end_date is an earlier date than start_date (row 9 above).

Example: Calculate the Difference between Two Dates

An interesting point about DATEDIF is that it is a hidden function in that it is not listed with other Date functions under the formula tab in Excel, which means:

  1. there is no dialog box available for entering the function and its arguments. 
  2. the argument tooltip does not 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 in order for it to be used, including typing a comma between each argument to act as a separator.

DATEDIF Example: Calculating the Difference in Days 

The steps below cover how to enter the DATEDIF function located in cell B2 in the image above that displays 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 second comma ( , ) following the cell reference A3.
  7. For the unit argument, type the letter D in quotes ("D" ) to tell the function we want to know 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 – should appear in cell B2 of the worksheet.
  11. When you click on cell B2 the complete formula =DATEDIF(A2,A3,"D") appears in the formula bar above the worksheet.
Was this page helpful?