Microsoft Office Tutorials and References
In Depth Information
When you enter a time that exceeds 24 hours, the associated date for the time increments accordingly. For ex-
ample, 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.)
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/2013 1:00:00 AM:
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.
Formatting dates and times
You have a great deal of flexibility 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.
One way to format dates and times is by selecting the cells and then using the Number Format control from the
Home ⇒ Number group (see Figure 6-1). This control offers two date formats and one time format. The date
formats are those specified as your Windows Short Date and Long Date formats.
When you create a formula that refers to a cell containing a date or a time, Excel may
automatically format the formula cell as a date or a time. Sometimes, this is very help-
ful; other times, it's completely inappropriate and downright annoying. Unfortunately,
you cannot turn off this automatic date formatting. You can, however, use a shortcut
key combination to remove all number formatting from the cell and return to the default
General format. Just select the cell and press Ctrl+Shift+~.
For more control over date and time formatting, select the cells and then use the Number tab of the Format Cells
dialog box, as shown in Figure 6-2. Here are ways to display this dialog box:
• Click the dialog box launcher icon of the Number group of the Home tab.
• Click the Number Format control and choose More Number Formats from the list that appears.
• Press Ctrl+1.