How Dates and Time Are Stored In Excel

Understanding how dates and time are stored in Excel can really help when dealing with them in spreadsheets. I’ve spent quite a lot of time trying to debug issues in spreadsheets. When those issues are date related, knowing how they act definitely helps.

Dates and time can be stored as text strings which are quite useless (in most circumstances).

In simple terms, Excel stores dates as whole numbers and stores time after the decimal point.

The image below shows 2 columns. In the first is a number formatted as a number. The second column shows that same number formatted as a date.

dates_time_1

As you can see, dates start at 1, with 1 being the 1st January 1900 (Zero is not a date). From then on, each day is just one bigger that the last. Simple, but clever. You can now just add 7 to any date to get the same day the week after etc etc.

…Calculating a future month by adding days can be trickier as all months don’t have the same amount of days. Once I’ve written it, I’ll put a link here that will talk about more advanced date functions…

The next image shows decimals in the first column and the same decimal formatted in time in the second.

dates_time_2

The number after the decimal point represents a fraction of a day. As you can see in row 2, each hour is equal to 0.041666667 (1 divided by 24).

A point to note!

When you manually enter dates into Excel you can do so using just 2 digits for the year. Excel guesses what century you might be wanting. Currently, when you enter anything between 00 and 29 Excel will assume you mean 2000 to 2029. Anything between 30 and 99 and excel will think you mean 1930 to 1999.

No ratings yet.

Please rate this