Microsoft Office Tutorials and References
In Depth Information
Chapter 6: Working with Dates and Times
Table 6-3: Time Entry Formats Recognized by Excel
Entry
Excel’s Interpretation
What Excel Displays
11:30:00 am
11:30 AM
11:30:00 AM
11:30:00 AM
11:30 AM
11:30:00 AM
11:30 pm
11:30 PM
11:30 PM
11:30
11:30 AM
11:30
13:30
1:30 PM
13:30
11 AM
11:00 AM
11:00 AM
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 non-date January 0, 1900.
If you’re using the 1904 date system, time values without an explicit date use January 1,
1904, as the date. The discussion that follows assumes that you are using the default
1900 date system.
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 the text
that follows in a cell, Excel interprets it as 11:30 a.m. on June 18, 2010. Its date/time serial number
is 40347.4791666667.
6/18/2010 11:30
When you enter a time that exceeds 24 hours, the associated date for the time increments
accordingly. For example, if you enter the following time into a cell, it is 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 entered without a date uses January 0, 1900, as the date.)
25:00:00
Similarly, if you enter a date and a time (and the time exceeds 24 hours), the date that you
entered is adjusted. The following entry, for example, is interpreted as 9/2/2010 1:00:00 AM:
9/1/2010 25:00:00
If you enter a time only (without an associated date), you’ll find that the maximum time that you
can enter into a cell is 9999:59:59 (just under 10,000 hours). Excel adds the appropriate number
of days. In this case, 9999:59:59 is interpreted as 3:59:59 PM on 02/19/1901. If you enter a time
that exceeds 10,000 hours, the time appears as a text string.
 
Search JabSto ::




Custom Search