Excel 2010 Bible

Part II: Working with Formulas and Functions

276

Figure 12.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).

On the CD
This workbook is available on the companion CD-ROM. The filename is time sheet.xlsm. The workbook
contains a few macros to make it easier to use. n

The week’s starting date appears in cell D5, and the formulas in column B 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)

FIGURE 12.9

An employee timesheet workbook.

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 example, an employee may start work at 10:00 PM and begin

lunch at 2:00 AM. Without the IF function, the formula returns a negative result.

The following formula in cell H17 calculates the weekly total by summing the daily totals in col-

umn H:

=SUM(H8:H14)