Microsoft Office Tutorials and References

In Depth Information

**Calculating the difference between two times**

The
TIMEVALUE
function converts a text string that looks like a time into a time serial

number. This formula returns
0.2395833333
, the time serial number for 5:45 a.m.:

=TIMEVALUE("5:45 am")

To view the result of this formula as a time, you need to apply number formatting to the

cell. The
TIMEVALUE
function doesn’t recognize all common time formats. For example, the

following formula returns an error because Excel doesn’t like the periods in “a.m.”

=TIMEVALUE("5:45 a.m.")

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,

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. 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 16.5 shows a worksheet that calculates the hours

worked. As you can see, the shift that spans midnight presents a problem (cell C3).

FIGURE 16.5

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