Microsoft Office Tutorials and References

In Depth Information

Figure 6-9 shows another example of a worksheet that manipulates times. This worksheet keeps track of hours

worked during a week (regular hours and overtime hours).

The week's starting date appears in cell D5, and the formulas in column C fill in the dates for the days of the

week. Times appear in the range D8:G14, and formulas in column H calculate the number of hours worked each

day. For example, the formula in cell H8 is

=IF(E8<D8,E8+1-D8,E8-D8)+IF(G8<F8,G8+1-G8,G8-F8)

The first part of this formula subtracts the time in column D from the time in column E to get the total hours

worked before lunch. The second part subtracts the time in column F from the time in column G to get the total

hours worked after lunch. I use IF functions to accommodate graveyard shift cases that span midnight — for ex-

ample, an employee may start work at 10:00 PM and begin lunch at 2:00 AM. Without the IF function, the for-

mula returns a negative result.

The following formula in cell H17 calculates the weekly total by summing the daily totals in column H:

=SUM(H8:H14)