Microsoft Office Tutorials and References
In Depth Information
If the direction of the time difference doesn’t matter, you can use the ABS function to return the
absolute value of the difference:
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:
In fact, another (even simpler) formula can do the job:
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: