Excel Practice
Lessons Lesson 103

Project: A Timesheet

About this lesson

A timesheet is overtime as MAX of hours-over-standard and zero. Pay is ordinary hours at one locked rate plus overtime at another. Underneath sit a SUM, a NETWORKDAYS and a SUMIF.

By the end you can

  • build a timesheet with overtime, two pay rates and a weekly total.
  • keep every rate in a cell so a pay rise is one edit.

Practises MAX SUM NETWORKDAYS SUMIF

The idea

The last project, and the one most people build for themselves. Overtime is the MAX floor from Everyday Math. Pay is two products added, with the rates locked in cells. The summary is three functions from three different modules.

Everything that might change lives in a cell: the standard day, the two rates. A timesheet with 15 typed into six formulas is a timesheet that will be wrong after the next pay review. It will look right until somebody checks.

The mistake to watch for

Overtime as a plain subtraction. Hours minus the standard day goes negative on a short day. Then it pays the person less than their ordinary hours. MAX with a floor of 0 is what overtime means. And the rates and the standard day live in cells. 15 typed into six formulas is wrong after the next pay review. It looks right until somebody checks.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, Monday's overtime: the hours above the standard day in B9, but never below zero. Use MAX and lock B9. This fills down.

To begin, type it exactly:

=MAX(C2-$B$9,0)

D2
Row A B C D E F G
1 Day Date Hours Overtime Pay
2 Mon 45369 8
3 Tue 45370 9.5
4 Wed 45371 8
5 Thu 45372 10
6 Fri 45373 7.5
7 Sat 45374 4
8
9 Standard day 8 Working days
10 Rate 15 Overtime hours
11 Overtime rate 22.5
12 Total pay

Every step

  1. 1

    In D2, Monday's overtime: the hours above the standard day in B9, but never below zero. Use MAX and lock B9. This fills down. To begin, type it exactly: =MAX(C2-$B$9,0).

    Hint. MAX with 0 as the floor.

  2. 2

    Read =MAX(C3-$B$9,0) and say what Tuesday's overtime is.

    Hint. Above the standard day.

  3. 3

    In E2, Monday's pay. The ordinary hours at the rate in B10, plus the overtime hours at the rate in B11. Lock both rates.

    Hint. Two products, added.

  4. 4

    In B12, the week's total pay.

    Hint. E2 to E7.

  5. 5

    In E9, the number of working days between the first and last dates, with NETWORKDAYS.

    Hint. First date, last date.

  6. 6

    In E10, write a formula using SUMIF that gives the total overtime hours for the week. That is the overtime column added where it is above zero.

    Hint. ">0".