How does Excel stores time values
We had already learned from the previous lesson (1900 and 1904 date systems in Excel) that Excel stores date values internally as date serial numbers. By default, Excel uses January 1, 1900 as the first date and matches it with the serial number 1. Click the following link to learn more about how Excel stores Date values.
Excel stores Time values as Decimal numbers. Twenty-four hours in a day are represented by fractions between 0 and 1. If 24 hours of time are represented by the fractions between 0 and 1, let us try to find the fractional values used to represent one hour, one minute and one second. Refer below table.
| Time component | Fraction | Decimal value |
|---|---|---|
| One Hour | 1/24 | 0.04166667 |
| One Minute | 1/(24*60) | 0.00069444 |
| One Second | 1/(24*60*60) | 0.00001157 |
Examples of Time decimal numbers
Now, to check above decimal numbers for corresponding time, let us test below examples.
04:45:00
The time 04:45:00 is made of 4 hours and 45 minutes. As per above table, 4 hours and 45 minutes can be represented by (4*0.04166667)+ (45*0.00069444) = 0.19791648.
13:20:12
The time 13:20:12 is made of 13 hours and 20 minutes and 12 seconds. As per above table, 13 hours and 20 minutes and 12 seconds can be represented by (13*0.04166667)+(20*0.00069444)+(12*0.00001157) = 0.55569435.
20:08:30
The time 20:08:30 is made of 20 hours and 8 minutes and 30 seconds. As per above table, 20 hours and 8 minutes and 30 seconds can be represented by (20*0.04166667)+(8*0.00069444)+(30*0.00001157) = 0.83923602.
Following image shows above three examples worked in an Excel worksheet. Note that the last Column is formatted as Time data, so that you can see the time equivalent of decimal number.
