Excel 2010 Bible

Part II: Working with Formulas and Functions

252

As you can see in Table 12.1, Excel is rather flexible when it comes to recognizing dates entered

into a cell. It’s not perfect, however. For example, Excel does not recognize any of the following

entries as dates:

l (^) June 18 2010
l Jun-18 2010
l (^) Jun-18/2010
Rather, it interprets these entries as text. If you plan to use dates in formulas, make sure that Excel
can recognize the date you enter as a date; otherwise, the formulas that refer to these dates will
produce incorrect results.
If you attempt to enter a date that lies outside of the supported date range, Excel interprets it as
text. If you attempt to format a serial number that lies outside of the supported range as a date, the
value displays as a series of hash marks (#########).

Understanding time serial numbers .........................................................................

When you need to work with time values, you extend the Excel date serial number system to

include decimals. In other words, Excel works with times by using fractional days. For example,

the date serial number for June 1, 2010 is 40330. Noon (halfway through the day) is represented

internally as 40330.5.

The serial number equivalent of one minute is approximately 0.00069444. The formula that fol-

lows calculates this number by multiplying 24 hours by 60 minutes, and dividing the result into 1.

The denominator consists of the number of minutes in a day (1,440).

=1/(24*60)

Similarly, the serial number equivalent of one second is approximately 0.00001157, obtained by

the following formula:

1 / 24 hours × 60 minutes × 60 seconds

In this case, the denominator represents the number of seconds in a day (86,400).

=1/(24*60*60)

If your worksheet uses many dates, you may need to search for a particular date by using the Find and

Replace dialog box (Home ➪ Editing ➪ Find & Select ➪ Find, or Ctrl+F). Excel is rather picky when it comes

to finding dates. You must enter the date as it appears in the formula bar. For example, if a cell contains a

date formatted to display as June 19, 2010, the date appears in the Formula bar using your system’s short

date format (for example, 6/19/2010). Therefore, if you search for the date as it appears in the cell, Excel

won’t find it. But it will find the cell if you search for date in the format that appears in the Formula bar.

Searching for Dates