Part II: Working with Formulas and Functions
Similarly, the serial number equivalent of one second is approximately 0.00001157, obtained
by the following formula:
=1/(24*60*60)
In this case, the denominator represents the number of seconds in a day (86,400).
In Excel, the smallest unit of time is one 1,000th of a second. The time serial number shown
here represents 23:59:59.999 (one 1,000th of a second before midnight):
0.99999999
Table 12.1 shows various times of day along with each associated time serial number.
TA B L E 12 .1 Times of Day and Their Corresponding Serial Numbers
Time of Day Time Serial Number
12:00:00 AM (midnight) 0.00000000
1:3 0:0 0 AM 0.06250000
7:3 0:0 0 AM 0.31250000
10:30:00 AM 0.43750000
12:00:00 PM (noon) 0.50000000
1:3 0:0 0 PM 0.56250000
4:30:00 PM 0.68750000
6:00:00 PM 0.75000000
9:00:00 PM 0.87500000
10:30:00 PM 0.93750000
Entering times
As with entering dates, you normally don’t have to worry about the actual time serial
numbers. Just enter the time into a cell using a recognized format. Table 12.2 shows some
examples of time formats that Excel recognizes.
TABLE 12.2 Time Entry Formats Recognized by Excel
Entry Excel Interpretation
11:3 0 : 0 0 A M 11:3 0 A M
11:3 0 : 0 0 A M 11:3 0 A M
11:3 0 PM 11:3 0 PM
11:3 0 11:3 0 A M
13:3 0 1:3 0 PM
