Excel Tips and Tricks / Business Reporting Question: Is there a function in Excel that I can use to calculate the number of days between 2 dates. I would like to work out the number of days between an invoice date and invoice payment date.

Answer: Yes, using the DATEDIF function

DATEDIF is not listed with other functions under the formula tab in Excel 2007. 

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")

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 below.

 

1. Enter the following dates into cells C2 and D2: 1/30/2010 and 5/18/2010. 
2. Click on cell E1 in the spreadsheet - this is where the function will be located.
3. Type " = datedif " in cell E1 
4. Type an opening round bracket " ( " after the function name in cell E1 
5. Click on cell C2 in the spreadsheet to enter the cell reference of the start_date into the function
6. Type a comma ( , ) in cell E1 after C2 to act as a separator between the two cell references in the function
7. Click on cell D2 in the spreadsheet to enter the cell reference of the end_date into the function after the first comma
8. Type a second comma ( , ) in cell E1 following the second cell reference as a second separator
9. 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
10. Type the closing bracket " ) "  
11. Press the ENTER key on the keyboard
12. The answer 108 should appear in cell E1 as there are 108 days between January 30th and May 18
    
     

You can follow the same process for the remaining dates.

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.