How to Subtract Dates in Excel


Formatting a Cell as a Date:
  • Right click on the cell to be formatted
  • Select the option Format Cells...
  • Ensure the Number tab is selected in the window that pops up
  • Select the option Date from the list of Categories and click OK

If you want to subtract dates in Excel, it helps if you first understand how dates are stored within Excel.

Dates are actually stored as positive integers in Excel. It is only the formatting of an Excel cell that causes a number to be displayed as a date, rather than a simple numerical value.

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.

Because dates are simply integers, you can subtract dates in Excel, in the same way that you can subtract any other integers, and the result from the subtraction of two dates is the number of days between the two dates.


Excel Date Subtraction Examples

The following spreadsheet shows three examples of subtracting dates in Excel.

The spreadsheet on the left shows the formulas used and the spreadsheet on the right shows the results.

 Formulas:
 ABC
1Start DateEnd DateDifference
203-Jan-201118-Jan-2011=B2-A2
319-Nov-198626-Nov-2020=B3-A3
409-Mar-201123-Jun-2012=B4-A4
 Results:
 ABC
1Start DateEnd DateDifference
203-Jan-201118-Jan-201115
319-Nov-198626-Nov-202012426
409-Mar-201123-Jun-2012472

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.