How does Excel Store Dates?
Pay attention to the animation below. There are ten dates in column A, all copied and placed in column B, then formatted to Number – General.
The result is as shown below
Excel stores date as integer numbers, known as the date serial number. 1 represents January 1, 1900, 2 represents January 2, 1900, and so on, maximum of 2,958,465 representing December 31, 9999.
The date before January 1, 1900, and after December 31, 9999 is not recognized as the date by Excel.
***Excel Big Secret ***
Pay attention to February 29, 1900, in the picture above, Excel treats 1900 to be a leap year, but it’s not.
Why does this happen? Does Excel has a “bug”? YES, this a bug, but not Excel’s bug. This bug “owned” by Lotus 123, a popular spreadsheet program at the time. Excel keeps this bug to make Excel compatible with the popular spreadsheet program at that time.
How does Excel Store Times?
What about the time? Does Excel store it as an integer number too? See the image below for the explanation.
Excel stores time in the decimal number between 0 and 1. Integer number for the date and decimal number for the time.
How are the decimal numbers generated? If 1 represents one day then
| 1 Hour | =1/24 | = 0.041667 |
| 1 Minute | =1/24/60 | = 0.000694 |
| 1 Second | =1/24/60/60 | = 0.0000115741 |
| 1 Minute 45 Seconds | = (1/24/60) + (45/24/60/60) |
| = 0.0012152778 |
| 1 Hour 30 Minutes 30 Seconds | = (1/24) + (30/24/60) + (30/24/60/60) |
| = 0.0628472222 |
How does Excel Store Dates and Times?
If you use the date and time simultaneously, how does Excel store the data?
The result is the integer number represents the date, and decimal numbers represent the time.
