Have you ever wondered how a date is actually stored in Excel? This article describes how Excel stores dates and covers an example of how this can be used in calculations.

If you add a date to an empty cell you can choose from a whole variety of date formats to display the date, but the actual data Excel stores is only a single number. The cell formatting simply tells Excel which date format to display this number in. Because dates are stored as numbers, you can do calculations with dates, such as date2 subtracted from date2 to give you the difference between the two.

As you type in a date, Excel recognises the formatting as you type it and will then display the date in that format, storing the data as a single number. This works provided you type in the date in a date format which Excel recognises, with separators such as "/" or "-". Excel will not recognise dot separators when dates are entered, although it can display them this way.

Dates can also be displayed by using date functions such as TODAY or NOW. Try typing =TODAY() into cell A1 and press the enter key. The cell will show today's actual date. If you save and close the file, then open it tomorrow, you'll see tomorrow's date. Try typing =NOW() into a different blank cell and press enter key. This time you'll see today's date and time. Excel uses the computer's system clock to display the correct date details for these date functions.

To see the stored number in the cell, rather than the displayed date, first select a cell containing a date - say cell A1 you used earlier to add the TODAY function. Then change the cell format to number. To do this right click the cell, choose Format Cells, ensure the Number tab is chosen, under Category choose General, and click OK to finish. Excel now displays a number. For example for the date 10/10/10 Excel shows the number 40461.

So where does 40461 come from? To reveal how this works, type the number 1 into a different empty cell. Check the cell formatting (right click, Format Cells, look in the number tab) and you'll see the cell is set to General format. Now change this to date format - and hey presto this shows as 01/01/1900 which is the base date for numbers in all Microsoft Office applications. So 40461 is the numbers of days elapsed starting from 01/01/1900!

Because the human race has evolved and standardised calendars over a long time, the pattern of days of the week and number of days in each month is relatively complicated, and so software applications build in the correct sequence from this seed date of 01/01/1900. So when a date is entered into Excel, the software looks up the seed number from the date and stores the number. (Apple software uses 1904 as the seed date, because 1900 was not a leap year).

So dates are stored as a number of days from this seed date. If we then create a calculation to subtract one date from another, we'll see the difference in days. To show this try entering two dates in separate empty cells next to each other, for example in cells D4 and E4, with the date in E4 later than the date in D4. Excel will display the dates in the entered format. In cell F4 we'll type in a calculation to subtract one date from the other. But before we do this, we need to consider the format of the F4 cell.

In Excel 2003, if you subtract one date from another, Excel will automatically set the target cell format to be the same as the source data format. So the result will be in DATE format - not very meaningful for us as we want to show the difference in days. So before doing the subtraction, format the cell in General format. In Excel 2007 the subtraction will correctly show in General format, so you don't need to set the cell format first.

Now type in the subtraction calculation in cell F4 as =E4-D4 and you see the result as a number (of days). For example if our subtraction is 05/6/10 - 01/6/10 we'll get the difference as 4 days. Normally we want to include the first day in the number of days so we add 1 to the calculation, making it E4-D4+1 and this us the correct days difference as 5 days.

There are a variety of date functions in Excel 2007 used for other calculations. For example NETWORKDAYS lets you calculate the number of working days between two dates; DAY, MONTH, or YEAR shows you the current day, month or year of a certain date.

Interested in exploring dates and date functions further in Excel? A good way would be to attend a training course to explore these and other functions in much more detail to really boost your Excel knowledge.