Microsoft Office Tutorials and References
In Depth Information
Summing times that exceed 24 hours
Using the ABS function (to calculate the absolute value) isn’t an option in this case because
it returns the wrong result ( 16 ). The following formula, however, does work:
=IF(B2<A2,B2+1,B2)-A2
16
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,” earlier in this
chapter.)
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 16.6 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 16.6
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. In other words, the answer is correct, but it
appears incorrect because cell B9 has the wrong number format.
Search JabSto ::




Custom Search