Calculate a Date Difference in Excel

If you want to calculate the difference between two dates in Excel, it is useful to understand that dates in Excel are stored as simple integers. It is only the formatting of an Excel 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 settings and computer systems, but most commonly:

1=1st January 1900
2=2nd January 1900
42369=31st December 2015

... and so on.

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 numeric 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 Excel date difference calculation. The calculation simply subtracts the date in cell B1 from the date in cell B2.

1Start Date:19-Oct-2010
2End Date:31-Jan-2011
3Date Difference:=B2-B1
1Start Date:19-Oct-2010
2End Date:31-Jan-2011
3Date 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 you can use any of the following methods:

Method 1 - Formatting Through the Ribbon Options

The easiest way to change cell formatting to the 'General' format is to select the cell(s) to be formatted and then select the General option from the drop-down menu in the ribbon. This is found in the 'Number' group on the Home tab (see below):

Excel Format Cells Drop Down Menu on Ribbon

Method 2 - Formatting Through the 'Format Cells' Dialog Box

This method can be used in earlier versions of Excel that do not have the ribbon:

  • Right click on the cell(s) to be formatted;
  • Select the option Format Cells... to display the 'Format Cells' dialog box;
  • Ensure the Number tab of the dialog box is selected;
  • From the Category: list, select the option General and click OK.