Dates is one of the most common data types worked with in Excel.  It’s probably also the most frustrating to work with!

Instead of a month, day, or year, Excel uses numbers to represent the date.  It’s complicated further by the fact that dates are also days, like Monday or Wednesday but Excel doesn’t store that information in the cells.

In order to understand how dates work, you need to understand how Excel stores the information.  It doesn’t use day, month and year but rather each date is assigned a serial number.
This serial number is created by using the total number of days from a specific date since the beginning of the year 1900.  In other words, 1 January 1900 is assigned the number 1, 2 January 1900 is number 2 and so on.  Of course, as time goes on, the numbers have gotten bigger and bigger.

Today’s date is 11 August 2015, the serial number assigned to it is 42227.  So this means that there have been 42 227 days since 1 January 1900.

It seems confusing, but it makes it a lot easier to add, subtract, and count days. A week from today would be 18 August 2015, in order to work out the serial number you would need to add 7 to 42227 which will give you 42234.
​
The best part of this, though, is that you don't have to worry about working it out, Excel does all the work for you.  All you need to do is make sure your dates are correctly formatted so that Excel can work with them as numbers, then you can do calculations and ask the questions you want to ask.

It is important to note that any date before 1 January 1900 is not recognized as a date in Excel. Excel does not work with “negative” date serial numbers on the number line.

To find out how to easily format your data, so that Excel recognises it as a date and not just text, watch our tutorial.

​A common problem in Excel is the ability to calculate the age of a person.

In this tutorial we show you step by step how to calculate a person's age using the current date.