How to Calculate the Difference Between Dates in Microsoft Excel

Forget the Big Bang theory. According to Microsoft Excel, the universe began in 1900. January 1st 1900 was day 1 in Excel, and it doesn't recognise dates earlier than this as actual dates.

So Excel supports a 1900 date system. When you type a date into Excel, even though you see the format of the date, it is actually converted to a serial number with 01/01/1900 being stored as number 1. Each consecutive day increases the number by one, so 02/01/1900 is day 2 and so on. If you were to convert the date 20/10/1979 to a number, you would get 29148.

The benefit of this is that you can perform calculations on dates values in a variety of different ways.



To see a date value displayed as a number, type date into a cell, and changing the number format to General, either by using the Format Cells dialog box (right-click Format Cells) or using the Format number drop-down in the middle of the Home tab:

An Example of Using a Date Difference

Let's say you want to work out somebody's age based on their date of birth.

If you were born on the 20th October 1979 (typed into Cell A1) and wanted to work out how many days had passed since then, you could use the TODAY() function, which returns today's date, whenever today is. The value of this will obviously change on a day-to-day basis!
So if you then type the following into cell C1:
=TODAY()-A1
this would give you the number of days that have passed since 20/10/1979.

If you want your age in years, you would use
=(TODAY()-A1)/365.25
You would probably wrap that in an =INT() (Integer) function so it only displays the whole (integer) number. After all, no-one wants to be 36.8 years old, when 36 will do!

Handy Formulas

Here are some handy formulas, assuming your date is in A1 (don't forget to adjust your formatting if required):

• To go forward a week from the date in A1, use =A1+7
• To work out how many week days have passed between the values in cells A1 and A2, use =NETWORKDAYS(A1,A2)
• To work out the date, 20 week days from A1, the it's =WORKDAYS(A1,20)