How to Subtract Dates in Excel

Dates in Excel

If you want to subtract dates in Excel, it helps to understand the way that Excel stores dates.

Dates are stored as positive integers in Excel. It is only the formatting of an Excel cell that causes its contents to be displayed as a date, rather than an integer.

The numbering system used for Excel dates varies with different computer systems, but most commonly, the numbering system is:

As Excel dates are internally stored as simple integers, you can subtract dates in Excel, in the same way that you can subtract any other integers.

I.e. to subtract dates in Excel:

  1. Type the start date and the end date into your spreadsheet;
  2. Subtract the start date from the end date;
  3. Ensure the result is formatted as a number.

    (The easiest way to do this is to select the cell and then select the 'General' format from the drop-down menu in the Home tab of the Excel ribbon).

The result from the subtraction formula is equal to the number of days between the two dates.

Excel Date Subtraction Examples

Column C of the following spreadsheet shows three examples of Excel date subtraction formulas. In each case, the date in column A is subtracted from the date in column B.

  A B C
1 Start Date End Date Difference
2 03-Jan-2011 18-Jan-2011 =B2-A2
3 19-Nov-1986 26-Nov-2020 =B3-A3
4 09-Mar-2011 23-Jun-2012 =B4-A4
  A B C
1 Start Date End Date Difference
2 03-Jan-2011 18-Jan-2011 15
3 19-Nov-1986 26-Nov-2020 12426
4 09-Mar-2011 23-Jun-2012 472

Note that, in the results spreadsheet above, the cells in columns A and B are formatted with a date format, but the cells in column C are formatted with the 'General' format.

Changing Excel Cell Formatting

If the result of your date subtraction is displayed as a date, but you require a simple number, you will need to change the formatting of the results cell, to display a number.

The easiest way to do this is to select the General formatting option from the drop-down format menu of the Excel ribbon. This is generally located in the 'Number' group, on the Home tab of the ribbon (see below):

Excel Format Cells Drop Down Menu on Ribbon With General Option Selected
Format a cell to display a number by selecting the 'General' format from the drop-down menu on the ribbon

See also How to Subtract Times in Excel  >>