Microsoft Office Tutorials and References

In Depth Information

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 re-

turns 08:30:00 (a difference of eight 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, calculating 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. If the work shift spans midnight, though, the result is an invalid negative time. For example, you may

start work at 10:00 PM and end work at 6:00 AM the next day. Figure 6-7 shows a worksheet that calculates the

hours worked. As you can see, the shift that spans midnight presents a problem.

Figure 6-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

In fact, another (even simpler) formula can do the job:

=MOD(B2-A2,1)