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.
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.
January 1, 1900 = 1
January 3, 1900 = 3
February 2, 1901 = 399
March 1, 2014 = 41699
While the date is stored as an integer, time is stored as a decimal fraction. You can find a few examples below.
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.
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.