Part II: Working with Formulas and Functions
254
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/2010 11:30 in a cell, Excel
interprets it as 11:30 a.m. on June 18, 2010. Its date/time serial number is 40347.479166667.When you enter a time that exceeds 24 hours, the associated date for the time increments accord-
ingly. For example, if you enter 25:00:00 into a cell, it’s interpreted as 1:00 a.m. on January 1,- 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/2010 25:00:00, for example, it’s interpreted as 9/19/2010 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 appropri-
ate 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 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.You format dates and times by selecting the cells and then using the Number tab of the Format
Cells dialog box, as shown in Figure 12.1. To display this dialog box, click the dialog box launcher
icon in the Number group of the Home tab. Or, click Number Format and choose More Number
Formats from the list that appears.The Date category shows built-in date formats, and the Time category shows built-in time formats.
Some formats include both date and time displays. Just select the desired format from the Type list
and then click OK.Tip
When you create a formula that refers to a cell containing a date or a time, Excel sometimes automatically for-
mats the formula cell as a date or a time. Often, this automation is very helpful; other times, it’s completely
inappropriate and downright annoying. To return the number formatting to the default General format, choose
Home ➪ Number ➪ Number Format and choose General from the drop-down list. Or, press Ctrl+Shift+~. n
If none of the built-in formats meets your needs, you can create a custom number format. Select
the Custom category and then type the custom format codes into the Type box. (See Chapter 24
for information on creating custom number formats.)