Formuladrill

Level 6: Work mode Exercise 103 of 118

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.

Functions used: MAX, MIN

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

More exercises in Work mode