Overtime on a timesheet
An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.
The task
From Imani in Payroll: "Hours above 40 in a week are paid at 1.5 times the hourly rate. For each person put the regular hours (at most 40) in D2:D4, the overtime hours in E2:E4 and the total pay in F2:F4."
What this exercise teaches
MIN and MAX are the short way to cap a value or stop it going negative, with no IF needed. Splitting the hours into two columns first keeps the pay formula simple and easy to check.
A hint if you get stuck
MIN(B2,40) caps the regular hours at 40. MAX(B2-40,0) gives the hours above 40 and never goes below zero. Pay is regular hours plus 1.5 times overtime hours, times the rate.
Try it now, free, no account needed
Your first 15 exercises on Formuladrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.
Open this exercise