Lessons Lesson 105

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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, type:

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

This fills down to D7.

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, type: =MAX(C2-$B$9,0). This fills down to D7.

    Hint. MAX with 0 as the floor.

  2. 2

    Read =MAX(C3-$B$9,0) and say what it returns.

    Hint. Above the standard day.

  3. 3

    In E2, total ordinary pay plus overtime pay. Lock both rates so it fills down.

    Hint. Two products, added.

  4. 4

    In B12, total E2:E7 with SUM.

    Hint. E2 to E7.

  5. 5

    In E9, count the working days from B2 to B7 with NETWORKDAYS.

    Hint. First date, last date.

  6. 6

    In E10, total the overtime hours above zero with SUMIF.

    Hint. ">0".