Page 287 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 244
Part II
Working with Formulas and Functions
Summing times that exceed 24 hours
Many people are surprised to discover that when you sum a series of times that exceed 24 hours, Excel
doesn’t display the correct total. Figure 13.7 shows an example. The range B2:B8 contains times that repre-
sent the hours and minutes worked each day. The formula in cell B9 is
=SUM(B2:B8)
As you can see, the formula returns a seemingly incorrect total (17 hours, 45 minutes). The total should
read 41 hours, 45 minutes. The problem is that the formula is displaying the total as a date/time serial num-
ber of 1.7395833, but the cell formatting is not displaying the date part of the date/time. The answer is
incorrect because cell B9 has the wrong number format.
FIGURE 13.7
Incorrect cell formatting makes the total appear incorrectly.
To view a time that exceeds 24 hours, you need to apply a custom number format for the cell so that square
brackets surround the hour part of the format string. Applying the number format here to cell B9 displays
the sum correctly:
[h]:mm
CROSS-REF For more information about custom number formats, see Chapter 24.
CROSS-REF
Figure 13.8 shows another example of a worksheet that manipulates times. This worksheet keeps track of
hours worked during a week (regular hours and overtime hours).
This workbook is available on the companion CD-ROM. The file name is time sheet.xlsm. The
ON the CD-ROM workbook contains a few macros to make it easier to use.
ON the CD-ROM
The week’s starting date appears in cell D5, and the formulas in column B fill in the dates for the days of the
week. Times appear in the range D8:G14, and formulas in column H calculate the number of hours worked
each day. For example, the formula in cell H8 is
=IF(E8<D8,E8+1-D8,E8-D8)+IF(G8<F8,G8+1-G8,G8-F8)

244