Microsoft Office Tutorials and References

In Depth Information

**Working with Negative Time Values**

Working with Negative Time Values

Because Excel stores dates and times as numeric values, you can add or subtract one from the

other. Figure 94-1 shows a simple example that tracks the number of hours worked. The total is

calculated in cell B8 by using this formula and formatting the cell using the [h]:mm number

format (see Tip 59 for details):

=SUM(B2:B6)

Cell B9 uses the following formula to calculate the amount of time required to make up a

40-hour work week:

=(40/24)-B8

Figure 94-1:
Cell B8 displays the time left in a 40-hour workweek.

If the number of hours worked exceeds 40 hours, the formula in cell B9 returns a negative

number. But, as you can see in Figure 94-2, Excel canâ€™t deal with a negative time value.

Figure 94-2:
Excel displays a series of hash marks for negative time values.