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




Custom Search