Hands on: Calculate hours and pay on an Excel spreadsheet

Give the range B5:H6 the Custom format h:mm AM/PM. Using 24-hour time enter some work periods. To record working overnight from Monday to Tuesday, enter 24:00 in cell C6 and 00:01 in D5.

This worker doesn’t charge his rest breaks to the client. It doesn’t matter when he takes them within the work period.

What matters is how long he was on a break. He measures his breaks in quarter-hours.

So format B7:H9 as Number, Fraction, ‘Up to one digit (?)’. but make the entries with a decimal. A one-and-a-quarter hour rest break is entered as 1.25 and displays as 114

To calculate the number of regular hours worked on Sunday enter

=MROUND(((B6-B5)*24),0.25)-B7

in cell B8.

The MROUND function is included with Excel 2007 but in earlier versions you may need to add it. To do that, click the Tools menu, select Add-Ins and tick Analysis Toolpak. With the formula entered, point to the lower right corner of cell B8 and drag along the row to H8.

The 24 in the formula converts decimal fractions of days into hours. MROUND will accept a formula as an argument. The second argument is 0.25 because the results are to be rounded to quarter hours.

Assuming the overtime rate applies after eight hours, enter

=IF(C8>8,(C8-8),0)

in C9 and drag along to H9. It’s not needed in B9 because Sunday is already at an overtime rate. It is now easy to calculate the daily earnings. Highlight B12:H14 and click the Accounting tool or right-click and choose Format cells, Number, Accounting. In B12 enter =B8*.

Press the F3 function key to display the available Names and choose Sunday. Click OK and the entry will be =B8*Sunday

In cell C12 enter

=(C8-C9)*Regular

in the same way. Drag this formula along to H12. Similarly in C13 enter

=C9*Overtime

and drag along to H13. Highlight B14:H14 and click the AutoSum tool to calculate each day’s earnings. In B15 enter

=SUM(B14:H14)

to display the week’s total earnings. To make the result look more like a timesheet than a spreadsheet, remove the checkmark next to Gridlines under the View tab in Excel 2007. In earlier versions of Excel, choose Options on the Tools menu, then View, Window Options, and remove the Gridlines’ checkmark.