1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Calculate the Number of Days Between Dates in Excel

Excel DATEDIF Function Text-only Tutorial

By

Calculate the Number of Days Between Dates in Excel

Calculate the Number of Days Between Dates

© Ted French

Note: If you have limited experience with Excel try the Excel DATEDIF Function Step by Step Tutorial which includes detailed information about using this function.

This tutorial is intended for users familiar with using Excel's date functions.

Excel DATEDIF Function Quick Tutorial

The DATEDIF function can be used to calculate the number of days, months, or years between two dates.

DATEDIF, however, is an "undocumented" function which means it is not listed with other functions under the formula tab in Excel.

To use the function you must type it manually into a cell on the worksheet rather than using the dialog box method available for other functions.

The syntax for the DATEDIF function is:

= DATEDIF ( start_date , end_date , unit )

The function has three arguments that need to be entered as part of the function:

  • start_date - the first or starting date.
  • end_date - the second or last date.
  • unit - tells the function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates.

The unit argument can also be a combination of days, months, and years:

  • "YM" - excludes years - calculates the number of months between two dates as if the dates were in the same year.
  • "YD" - excludes years - calculates the number of days between two dates as if the dates were in the same year.
  • "MD" - excludes months - calculates the number of days between two dates as if the dates were in the same month and year.

Calculate the Number of Days Between Dates Example

As seen in the image above, this example, DATEDIF will be used to find the number of days between the dates January 30, 2009 and May 18, 2009. The unit argument, as a result, will be "D".

The start and end dates will be entered into worksheet cells and their cell references entered as the function's arguments rather then the dates themselves.

This approach makes it easy to adjust the dates used in the formula without editing the formula itself.

Note: Commas are used as separators between the function's three arguments.

  1. Enter the following dates into cells D1 and D2: 1/30/2009 and 5/18/2009

  2. Click on cell E1 in the worksheet - this is where the function will be located

  3. Type the formula " = DATEDIF ( D1 , D2 , "D" ) " into cell E1

  4. Press the ENTER key on the keyboard.

  5. The answer 108 should appear in cell E1 as there are 108 days between January 30th and May 18

  6. If you get a #NUM! error in the cell where your function is located, it means that the start_date is larger (later in the year) than the end_date

  7. If you click on cell E1, the complete function = DATEDIF ( D1 , D2 , "D" ) appears in the formula bar above the worksheet

©2014 About.com. All rights reserved.