If you don't fully understand how dates and times work in Excel, attempting to use them is about as intuitive as setting the clock on a VCR. This month we'll look at how to create a useful employee time sheet ù and in the process try to clear up some of the confusion associated with adding and subtracting times in a spreadsheet.

The employee time sheet illustrated shows the finished product: a worksheet that computes total hours worked each day, as well as a weekly total broken down into regular hours and overtime hours (TimeSheet is on this month's cover CD).

[insert image: employee.tif]

Caption: The workbook timesheet.zip calculates daily and weekly hours worked, including overtime

Start by entering the descriptive labels. Rows 2 to 7 contain text only (no formulas). You can, of course, format the text to your liking. While you're at it, enter the text in the Weekly Total box.

Now you're ready for the formulas. Activate cell B8 and enter =D5. This formula simply picks up the week's starting date, which is entered in cell D5. In cell B9, enter =B8+1 and then copy this formula to the range B10:B14. Next, activate cell C8 and enter =B8. Copy this formula to the range C9:C14. You'll need to format the dates in column C to display weekday names. To accomplish this task, select the range C8:C14 and choose Format-Cells. In the Format Cells dialogue box, click the Number tab. Select the Custom category and then enter dddd in the box labelled Type. Click OK to close the dialogue box.

Next, activate cell H8 and enter the formula =IF(E8<D8,E8+1-D8,E8-D8)+IF(G8<F8,G8+1-G8,G8-F8).

How it works

The first part of this formula subtracts the time in column D from the time in column E to get the total number of hours worked before lunch. The second part subtracts the time in column F from the time in column G to get the total number of hours worked after lunch. The IF functions accommodate graveyard-shift cases that span midnight ù for example, an employee may start work at 10pm and begin "lunch" at 2am. Without the IF statement, the formula would return a negative result.

Copy the formula in cell H8 to the range H9:H14. Now you need to format these cells to display time values. Select the range H8:H14, choose Format-Cells, and click the Number tab. Select the Time category, click the first option in the Type list box, and click OK.

Next up: the weekly totals. Activate cell H17 and enter =SUM(H8:H14). This formula computes the sum of the daily totals in column H. Now enter into cell H18 the formula =MIN(H17,1+TIME(40,0,0)). This formula, which assumes a 40-hour week, returns the smaller of two values: the total number of hours worked, or 40 hours. The final formula, in cell H19, is =H17-H18. This formula subtracts the number of regular hours from total hours to yield the number of overtime hours. If your standard working week consists of something other than 40 hours, you'll have to change the formula in cell H18 accordingly.

One critical step remains: formatting the time values in the range H17:H19. Since these cells are likely to display time values that exceed 24 hours, you'll need to use a custom time format. Select the range H17:H19, choose Format-Cells, and click the Number tab. Select the Custom category, enter [h]:mm into the Type box, and click OK. Using square brackets around the hour part of the format code allows the spreadsheet to display more than 24 hours.