Excel 2010 Bible

Part II: Working with Formulas and Functions

274

Calculating the difference between two times ..........................................................

Because times are represented as serial numbers, you can subtract the earlier time from the later

time to get the difference. For example, if cell A2 contains 5:30:00 and cell B2 contains 14:00:00,

the following formula returns 08:30:00 (a difference of 8 hours and 30 minutes):

=B2-A2

If the subtraction results in a negative value, however, it becomes an invalid time; Excel displays a

series of hash marks (#######) because a time without a date has a date serial number of 0. A

negative time results in a negative serial number, which cannot be displayed — although you can

still use the calculated value in other formulas.

If the direction of the time difference doesn’t matter, you can use the ABS function to return the

absolute value of the difference:

=ABS(B2-A2)

This “negative time” problem often occurs when calculating an elapsed time — for example, calcu-

lating the number of hours worked given a start time and an end time. This presents no problem if

the two times fall in the same day. But if the work shift spans midnight, the result is an invalid

negative time. For example, you may start work at 10:00 p.m. and end work at 6:00 a.m. the next

day. Figure 12.7 shows a worksheet that calculates the hours worked. As you can see, the shift that

spans midnight presents a problem (cell C3).

FIGURE 12.7

Calculating the number of hours worked returns an error if the shift spans midnight.

Using the ABS function (to calculate the absolute value) isn’t an option in this case because it

returns the wrong result (16 hours). The following formula, however, does work:

=IF(B2<A2,B2+1,B2)-A2