How are dates stored in Excel?

Have you ever wondered how dates are actually stored in Excel?

As many people need to use dates in formulas, Excel stores the dates as numbers. For example, this allows dates in two columns to be subtracted.

This does mean you have to consider date formats when entering dates into cells. Excel recognises separators such as "/" or "-" but does not recognise dot separators. However, Excel can display dates with dot separators.

To see the stored number in a cell rather than the date, select Format Cells, Number and then General from the Category column. For example, the date 1/1/12 displays 40909.

This is the number of days since the earliest allowed date. Try typing 1 into a different cell and formatting the cell as a date. You will see that the earliest date that can be stored is 1/1/1900.

So what is the largest date that Excel can store?
31/12/9999!

Related forum posts:

Converting dates

I have a range of dates that I have exported into Excel from another system. I need to compare these dates with another to get a period of time. However, Excel is reading the exported dates as text, not as dates. I have tried doing Format - Cells etc. which has no effect. Can anyone think of a way to convert these to dates or even just to numbers? Thanks Tristan
Read forum post

Converting Date Format

My question sounds quite silly- I
Read forum post

Using Dates Within Excel

Read article