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.
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.
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.
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:
This method can be used in earlier versions of Excel that do not have the ribbon: