Course / Challenges

Challenge: weekly payroll

Turn shift times into hours, pay overtime above a limit, then work out gross pay, tax, take-home pay and department totals. Advanced · 10 min · Time arithmetic, MAX, $ references, SUMIF

It's Friday at a busy neighbourhood bakery, and the payroll is due. Everyone works the same shift on each of their days, so the timesheet records each person's start and finish times and how many days they worked this week.

Hours above the weekly limit in N1 are paid at the overtime multiplier in N2, and tax is withheld at the flat rate in N3. Point your formulas at those cells instead of typing the numbers, so next year's changes take one edit.

> Excel stores a time of day as a fraction of a day: 6:00 AM is 0.25 and 12:00 PM is 0.5. Keep that in mind for the first step.

Steps

1. In G2:G13, work out each person's total hours for the week as a plain number: 8½ hours a day for 5 days should show 42.50.

2. In H2:H13, show the overtime hours: anything above the weekly limit in N1. Overtime can never be negative, so anyone under the limit should show 0.

3. In I2:I13, work out gross pay: the normal hours at the person's rate, plus the overtime hours at their rate times the multiplier in N2.

4. Fill J2:J13 with the tax withheld at the rate in N3, and K2:K13 with net pay: what actually lands in each person's bank account.

5. In N6:N8, total the gross pay for each department. Use one formula that fills down and finds each department's people by itself.

Remember

• Times are fractions of a day: (finish − start) × 24 gives hours.
• MAX(0, hours − limit) gives overtime that never goes negative.
• Keep the limit, multiplier and tax rate in cells and lock them with $.
• SUMIF(dept column, dept, pay column) totals each group in one formula.

Previous: Challenge: the league table · Next: Challenge: customer survey results