Page 269 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 226
Part II
Working with Formulas and Functions
Searching for Dates
f your worksheet uses many dates, you may need to search for a particular date by using the Find And
IReplace dialog box (which you can access by choosing Home ➪ Editing ➪ Find & Select ➪ Find or by
pressing Ctrl+F). Excel is rather picky when it comes to finding dates. You must enter a full four-digit year into
the Find What field in the Find dialog box. In addition, you must enter the date in the same format used to dis-
play dates in the Formula bar.
Understanding time serial numbers
When you need to work with time values, you simply 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, 2007, is 39234. Noon (halfway through the day) is represented internally as
39234.5.
The serial number equivalent of one minute is approximately 0.00069444. The formula that follows calcu-
lates 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 follow-
ing formula: 1 divided by 24 hours times 60 minutes times 60 seconds. In this case, the denominator repre-
sents the number of seconds in a day (86,400).
=1/(24*60*60)
In Excel, the smallest unit of time is one one-thousandth of a second. The time serial number shown here
represents 23:59:59.999 (or one one-thousandth of a second before midnight):
0.99999999
Table 13.2 shows various times of day along with each associated time serial numbers.
TABLE 13.2
Times of Day and Their Corresponding Serial Numbers
Time of Day Time Serial Number
12:00:00 AM (midnight) 0.00000000
1:30:00 AM 0.06250000
3:00:00 AM 0.12500000
4:30:00 AM 0.18750000
6:00:00 AM 0.25000000
7:30:00 AM 0.31250000
9:00:00 AM 0.37500000

226