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
Search JabSto ::




Custom Search