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.