Page 270 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 227
Working with Dates and Times
Time Serial Number
Time of Day
0.43750000
10:30:00 AM
0.50000000
12:00:00 PM (noon)
1:30:00 PM
0.56250000
3:00:00 PM
0.62500000
0.68750000
4:30:00 PM
0.75000000
6:00:00 PM
0.81250000
7:30:00 PM
0.87500000
9:00:00 PM
0.93750000
10:30:00 PM
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 13.3 shows some examples of time formats that Excel 13
recognizes.
TABLE 13.3
Time Entry Formats Recognized by Excel
Entry Excel’s Interpretation
11:30:00 am 11:30 AM
11:30:00 AM 11:30 AM
11:30 pm 11:30 PM
11:30 11:30 AM
13:30 1:30 PM
Because the preceding samples don’t have a specific day associated with them, Excel (by default) uses a date
serial number of 0, which corresponds to the nonday January 0, 1900. Often, you’ll want to combine a date
and time. Do so by using a recognized date-entry format, followed by a space, and then a recognized time-
entry format. For example, if you enter 6/18/2007 11:30 in a cell, Excel interprets it as 11:30 a.m. on June
18, 2007. Its date/time serial number is 39251.4791666667.
When you enter a time that exceeds 24 hours, the associated date for the time increments accordingly. For
example, if you enter 25:00:00 into a cell, it’s interpreted as 1:00 AM on January 1, 1900. The day part of
the entry increments because the time exceeds 24 hours. Keep in mind that a time value without a date uses
January 0, 1900 as the date.
Similarly, if you enter a date and a time (and the time exceeds 24 hours), the date that you entered is
adjusted. If you enter 9/18/2007 25:00:00, for example, it’s interpreted as 9/19/2007 1:00:00 AM.
227