How excel stores date and time

If you want to use date and time efficiently in Excel, you first have to learn how Excel stores these values.

At first, it may seem that Excel stores dates and times as text values (for example January 12, 2014). In fact, these values are nothing but numbers, formatted in a way that is easily recognizable by a person.

Date

The date is stored as an integer. Excel starts counting dates from January 1, 1900 24:00:00, so the number 1 is treated as January 1, 1900, 2 is treated as January 2, 1900 and so on. Look at the following example.

Example 1:

January 1, 1900 = 1 

January 3, 1900 = 3 

February 2, 1901 = 399 

March 1, 2014 = 41699

Time

While the date is stored as an integer, time is stored as a decimal fraction. You can find a few examples below.

Example 2:

24:00:00 = 0 

12:00:00 = 0.5 

11:00:00 p.m. = 0.958333333 

11:59:59 p.m. = 0.999988426

When a cell contains both the date and time, then the date will be represented by a total and the time by a fractional part of the number.

Example 3:

March 5th, 1920 13:51 = 7370.577731

December 11, 2000 18:11 = 36871.75803

Skillshare’s usually $10 a month, but because you’re an OfficeTuts reader you can get your first month free by clicking here. That’s unlimited access to over 18,000 classes on Excel, VBA, and much more.