Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

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-8 shows a worksheet that calculates the hours worked. As you can see, the

shift that spans midnight presents a problem.

Figure 6-8:
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)

Negative times
are
permitted if the workbook uses the 1904 date system. To switch to

the 1904 date system, choose Office➜Excel Options and then navigate to the When

Calculating This Workbook section of the Advanced tab. 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.

Converting from military time

Military time
is expressed as a four-digit number from 0000 to 2359. For example, 1:00 AM is

expressed as 0100 hours, and 3:30 PM is expressed as 1530 hours. The following formula

converts such a number (assumed to appear in cell A1) to a standard time: