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
Note: Commas are used as separators between the function's three arguments. For help with this example, see the image above.
- Enter the following dates into cells D1 and D2: 1/30/2009 and 5/18/2009
- Click on cell E1 in the spreadsheet - this is where the function will be located.
- Type " = datedif " in cell E1.
- Type an opening round bracket " ( " after the function name in cell E1.
- Click on cell D1 in the spreadsheet to enter the cell reference of the start_date into the function.
- Type a comma ( , ) in cell E1 after D1 to act as a separator between the two cell references in the function.
- Click on cell D2 in the spreadsheet to enter the cell reference of the end_date into the function after the first comma.
- Type a second comma ( , ) in cell E1 following the second cell reference as a second separator.
- For the unit argument, type the letter D in quotes ("D" ) in cell E1 after the second comma to tell the function we want to know the number of days between the two dates.
- Type the closing bracket " ) " .
- Press the ENTER key on the keyboard.
- The answer 108 should appear in cell E1 as there are 108 days between January 30th and May 18.
- If you click on cell C1, the complete function = DATEDIF ( D1 , D2 , "D" ) appears in the formula bar above the worksheet.
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.
Related Tutorials