Chapter 17: Using Formulas with Tables and Conditional Formatting. . . . . . . . . . . . . . .
12
Because the preceding samples don’t have a specific day associated with them, Excel 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/2016
11:30 into a cell, Excel interprets it as 11:30 AM on June 18, 2016. Its date/time serial
number is 42539.47917.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 you
entered is adjusted. If you enter 9/18/2016 25:00:00, for example, it’s interpreted as
9/19/2016 1:00:00 AMIf you enter a time only (without an associated date) into an unformatted cell, the maxi-
mum time 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 PM 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. 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.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.When you create a formula that refers to a cell containing a date or a time, Excel sometimes automatically formats
the formula cell as a date or a time. Often, this automation is helpful; at 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 just press Ctrl+Shift+~ (tilde).
Problems with dates
Excel has some problems when it comes to dates. Many of these problems stem from the fact
that Excel was designed many years ago. Excel designers basically emulated the Lotus 1-2-3