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

Custom Search