Excel 2010 Bible

Chapter 12: Working with Dates and Times

275

Tip
Negative times are permitted if the workbook uses the 1904 date system. To switch to the 1904 date system, use
the Advanced section of the Excel Options dialog box. Select the Use 1904 Date System option. But beware!
When changing the workbook’s date system, if the workbook uses dates, the dates will be off by four years For
more information about the 1904 date system, see the sidebar “Choose Your Date System: 1900 or 1904,” ear-
lier in this chapter. n

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 12.8 shows an example. The range B2:B8 contains

times that represent 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 number 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 12.8

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-Reference
For more information about custom number formats, see Chapter 24. n