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.
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
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.
