Microsoft Office Tutorials and References

In Depth Information

Negative times are permitted if the workbook uses the 1904 date system. To switch to

the 1904 date system, choose Office
⇒
Excel Options and then navigate to the When Cal-

culating This Workbook section of the Advanced tab. Place a check mark next to the

Use 1904 Date System option. But beware! When changing the workbook's date sys-

tem, if the workbook uses dates, the dates will be off by four years.

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 6-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)

Figure 6-8:
Incorrect cell formatting makes the total appear incorrectly.

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 be-

cause cell B9 has the wrong number format.

To view a time that exceeds 24 hours, you need to change the number format for the cell so square brackets sur-

round the
hour
part of the format string. Applying the number format here to cell B9 displays the sum correctly:

[h]:mm