How to Subtract Dates in Excel

Related Page:
Subtract Time 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

Before learning how to subtract dates in Excel, it is important that 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:
  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
 Results:
  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 of the above example, 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.

Return to the Basic Excel Page

Return to the ExcelFunctions.net Home Page

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