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

Custom Search