Pay and overtime

Turn decimal hours into pay, split them into regular and overtime hours with MIN and MAX, and apply an overtime multiplier. Intermediate · 5 min · Hours × rate, MIN as a cap, MAX(0, …), Overtime multiplier

Once hours are decimals, pay is a multiplication: hours × rate. Overtime adds a twist. Hours over a weekly threshold earn a higher rate, so each person's week splits in two:

Regular hours: all of them, up to the threshold. That's MIN(hours, threshold).
Overtime hours: whatever is over the threshold, and never less than zero. That's MAX(0, hours - threshold).

The sheet holds a week of hours for a small call-centre team. The pay rules live in B10:B12, so they're easy to change later.

Steps

Start with basic pay, as if every hour earned the normal rate. In C2:C7, multiply the hours by the hourly rate in B10. Lock it as $B$10 so it stays put as you fill down.
Regular hours stop at the threshold in B11. In D2:D7, use MIN to take whichever is smaller: the agent's hours or the threshold.
Now the overtime. In E2:E7, subtract the threshold in B11 from the hours, wrapped in MAX(0, …) so anyone under the threshold gets 0 rather than a negative number.
Overtime pays the hourly rate times the multiplier in B12. In F2:F7, add regular hours × rate to overtime hours × rate × multiplier.
A new contract starts overtime after 37.5 hours. Change B11 to 37.5 and watch the last three columns update.

Remember

Pay = decimal hours × rate, with the rate in one locked cell such as $B$10.
MIN(hours, threshold) caps the regular hours.
MAX(0, hours - threshold) gives the overtime and never goes negative.
Gross pay = regular × rate + overtime × rate × multiplier.

Previous: Timesheets and hours worked · Next: Weeks, months and quarters in reports