Page 286 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 243
Working with Dates and Times
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 for-
mula returns 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 is not permitted.
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 PM and end work at 6:00 AM the next day. Figure 13.6 shows a worksheet
that calculates the hours worked. As you can see, the shift that spans midnight presents a problem (cell C3). 13
FIGURE 13.6
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

TIP Negative times are permitted if the workbook uses the 1904 date system. To switch to the
TIP
1904 date system, use the Advanced section of the Excel Options dialog box. Place a check
mark next to the Use 1904 Date System option. But beware! When changing the workbook’s date system, if
the workbook uses dates, the dates will be off by four years For more information about the 1904 date sys-
tem, see the sidebar titled “Choose Your Date System: 1900 or 1904,” earlier in this chapter.

243