Page 291 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 248
Part II
Working with Formulas and Functions
Working with non-time-of-day values
Sometimes, you may want to work with time values that don’t represent an actual time of day. For example,
you may want to create a list of the finish times for a race or record the time you spend jogging each day.
Such times don’t represent a time of day. Rather, a value represents the time for an event (in hours, minutes,
and seconds). The time to complete a test, for example, may be 35 minutes and 45 seconds. You can enter
that value into a cell as:
Excel interprets such an entry as 12:35:45 AM, which works fine. (Just make sure that you format the cell
so that it appears as you like.) When you enter such times that do not have an hour component, you must
include at least one zero for the hour. If you omit a leading zero for a missing hour, Excel interprets your
entry as 35 hours and 45 minutes.
Figure 13.10 shows an example of a worksheet set up to keep track of a person’s jogging activity. Column A
contains simple dates. Column B contains the distance in miles. Column C contains the time it took to run
the distance. Column D contains formulas to calculate the speed in miles per hour. For example, the for-
mula in cell D2 is
=B2/(C2*24)
FIGURE 13.10 00:35:45
This worksheet uses times not associated with a time of day.

Column E contains formulas to calculate the pace, in minutes per mile. For example, the formula in cell E2 is

=(C2*60*24)/B2
Columns F and G contain formulas that calculate the year-to-date distance (using column B) and the cumu-
lative time (using column C). The cells in column G are formatted using the following number format
(which permits time displays that exceed 24 hours):

[hh]:mm:ss
You can also access the workbook shown in Figure 13.10 on the companion CD-ROM. The file
ON the CD-ROM is named jogging log.xlsx.
ON the CD-ROM

248