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
