Calculate a Date Difference in Excel

If you want to calculate a date difference in Excel, it is useful to understand that dates in Excel are stored as simple integers. It is only the formatting of the cell that causes the value to be displayed as a date, rather than as a simple number.

The numbering system used for Excel dates varies with different computer systems, but most commonly, the number 1 represents the date 01-Jan-1900, the number 2 represents 02-Jan-1900, ... , the number 40544 represents 01-Jan-2011, etc.

Therefore, when you want to calculate a date difference in Excel, you can simply subtract the two dates, in the same way as you would subtract any other numerical values in Excel. The resulting value tells you the number of days between the two dates. This is shown in the example below.


Excel Date Difference Example

Cell B3 of the following spreadsheet shows a simple example of a date difference in Excel. The calculation simply subtracts the date in cell B1 from the date in cell B2.

The formula used is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right.

 Formulas:
  A B
1 Start Date: 19-Oct-2010
2 End Date: 31-Jan-2011
3 Date Difference: =B2-B1
 Results:
  A B
1 Start Date: 19-Oct-2010
2 End Date: 31-Jan-2011
3 Date Difference: 104

In the example above, the date in cell B1 is internally represented by the number, 40470 and the date in cell B2 is internally represented by the number 40574. Subtracting these two numbers gives the result 104.

I.e. There are 104 days between the dates 19-Oct-2010 and 31-Jan-2011.


Formatting the Result

When you calculate a date difference in Excel the cell containing the result should display an integer. If not, this may be due to incorrect formatting of the cell.

In this case, you will need to format the cell to have the Excel 'General' format. To do this:

  • Right click on the cell(s) to be formatted
  • Select the option Format Cells...
  • Ensure the Number tab is selected in the window that pops up
  • Select the option General from the list of Categories and click OK
Return to the Excel-Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net