Microsoft Office Tutorials and References
In Depth Information
Understanding time serial numbers
When you need to work with time values, you simply extend Excel's date serial number system to include
decimals. In other words, Excel works with times by using fractional days. For example, the date serial number
for June 18, 2013, is 41078. Noon (halfway through the day) is represented internally as 41078.5.
The serial number equivalent of 1 minute is approximately 0.00069444. The formula that follows calculates this
number by multiplying 24 hours by 60 minutes and then dividing the result into 1. The denominator consists of
the number of minutes in a day (1,440).
=1/(24*60)
Similarly, the serial number equivalent of 1 second is approximately 0.00001157, obtained by the following for-
mula (1 divided by 24 hours times 60 minutes times 60 seconds). In this case, the denominator represents the
number of seconds in a day (86,400).
=1/(24*60*60)
In Excel, the smallest unit of time is one one-thousandth of a second. The time serial number shown here rep-
resents 23:59:59.999, or one one-thousandth of a second before midnight:
0.99999999
Table 6-2 shows various times of day, along with each associated time serial number.
Table 6-2: Times of Day and Their Corresponding Serial Numbers
Time of Day Time Serial Number
12:00:00 AM (midnight) 0.0000
1:30:00 AM
0.0625
3:00:00 AM
0.1250
4:30:00 AM
0.1875
6:00:00 AM
0.2500
7:30:00 AM
0.3125
9:00:00 AM
0.3750
10:30:00 AM
0.4375
12:00:00 PM (noon)
0.5000
1:30:00 PM
0.5625
Search JabSto ::

Custom Search