Page 180 - Excel Timesaving Techniques for Dummies

34_574272 ch30.qxd 10/1/04 10:50 PM Page 165
You Do the Date Math!
General number format to exact duplicates copied to Keep in mind that Excel can still compute 165
the cell range C2:C7.) elapsed time between date and times entered
as text. The program can even do calculations
between one date or time entered as text and
the other entered as an appropriate date or
time value.

You Do the Date Math!

Most of the date formulas that you build are
designed to calculate the number of days or years
that have elapsed between two dates. To do this,
• Figure 30-1: What you see is not always what you get in
you build a simple formula that subtracts the later
terms of date and time entries.
date from the earlier date. The only problem is that
the result you get from such date arithmetic usually
As column C of the sample worksheet shown in
doesn’t look like the answer you want. This is
Figure 30-1 reveals, Excel doesn’t treat the dates and because Excel gets on the old automatic-formatting
times that you enter as simple text entries. Any
bandwagon and assigns an inappropriate date for-
entry with a format that resembles one of the date mat to the result that really is the number of elapsed
and time number formats utilized by Excel is auto-
days.
matically displayed in that date and time format. At
the same time, the entry is converted, quietly Figure 30-2 illustrates the typical situation. Here, you
behind the scenes, into a serial number. want to calculate the years of service for a group of
employees by subtracting the date of their retire-
In the case of dates, this serial number represents
ments from their start dates. On the surface, this
the number of days that have elapsed since the seems simple enough: All you have to do is subtract
beginning of the 20th century so that January 1, 1900,
the date value in column E from the date value in
is serial number 1; January 2, 1900, is serial number column F so that the first formula in cell G2 is
2; and so forth. In the case of times, this serial num-
ber is a fraction that represents the number of hours, =E2-F2
minutes, and seconds that have elapsed since mid-
night, which is serial number 0.00000000. For exam-
ple, 12:00:00 p.m. (noon) is serial number 0.50000000;
11:00:00 p.m. is 0.95833333; and so forth.

As long as you make a date or time entry that con-
forms to a recognized date or time format, Excel
enters it as one of these date or time serial numbers.
The only time the program doesn’t make this adjust-
ment is when you enter a date or time in an unrecog-
nized format or specifically enter or import the date
or time as a text entry.
• Figure 30-2: Calculating the difference between a stop
and start date.