Microsoft Office Tutorials and References
In Depth Information
Formatting dates and times
TABLE 16.3 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 p m
11:3 0 PM
11:3 0
11:3 0 A M
13:30
1:30 PM
Because the preceding samples don’t have a specifi c day associated with them, Excel uses
a date serial number of 0, which corresponds to the nonday January 0, 1900. Often, you’fill
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/2013
11:30 in a cell, Excel interprets it as 11:30 a.m. on June 18, 2013. Its date/time serial
number is 41443.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 in a cell, it’s interpreted as 1:00 a.m. 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/2013 25:00:00 , for example, it’s interpreted as
9/19/2013 1:00:00 a.m.
If you enter a time only (without an associated date) into an unformatted cell, the
maximum time that you can enter into a cell is 9999:59:59 (just less than 10,000 hours). Excel
adds the appropriate number of days. In this case, 9999:59:59 is interpreted as 3:59:59 p.m.
on 02/19/1901. If you enter a time that exceeds 10,000 hours, the entry is interpreted as a
text string rather than a time.
Formatting dates and times
You have a great deal of fl exibility in formatting cells that contain dates and times. For
example, you can format the cell to display the date part only, the time part only, or both
the date and time parts.
You format dates and times by selecting the cells and then using the Number tab of the
Format Cells dialog box, shown in Figure 16.1. To display this dialog box, click the dialog
box launcher icon in the Number group of the Home tab, or click the Number Format control
and choose More Number Formats from the list that appears.
Search JabSto ::




Custom Search