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

Custom Search