Challenge Scenario
In payroll accounting and human resources, employees who work more than standard full-time hours (40 hours per work week) are legally entitled to Overtime Pay calculated at 1.5 times their standard hourly rate.
As a Payroll Specialist, you are processing the bi-weekly timesheets for four hourly team members:
- Regular Hours (Column D): Standard hours capped at 40 using
=MIN(B2, 40).
- Overtime Hours (Column E): Excess hours above 40 using
=MAX(0, B2-40).
- Gross Pay (Column F): Calculate regular pay plus 1.5x overtime pay:
=(D2*C2)+(E2*C2*1.5).
- Total Overtime Hours Logged (Cell E7): Sum all overtime hours using
SUM(E2:E5).
- Total Payroll Disbursement (Cell F7): Sum total gross payroll using
SUM(F2:F5).
Your Sheet Layout
Understanding the payroll computation table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Employee, Hours, Rate |
Timesheet Data |
Total hours logged and base hourly pay rates |
Read-only time card logs. |
D2:D5 |
Regular Hrs |
Standard Time |
=MIN(B2, 40) |
Caps standard hours at 40 maximum. |
E2:E5 |
Overtime Hrs |
Overtime Time |
=MAX(0, B2-40) |
Captures hours exceeding 40 (or 0 if under). |
F2:F5 |
Gross Pay ($) |
Wage Calculation |
=(D2*C2)+(E2*C2*1.5) |
Total pay including 1.5x overtime rate. |
E7 |
Total Overtime Hours |
Summary |
=SUM(E2:E5) |
Combined overtime hours worked (17 hrs). |
F7 |
Total Gross Payroll |
Summary |
=SUM(F2:F5) |
Total payroll payout ($4,631.50). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D5, calculate regular hours capped at 40 using MIN.
2
In cells E2:E5, calculate overtime hours exceeding 40 using MAX.
3
In cells F2:F5, calculate gross pay (= (Regular * Rate) + (Overtime * Rate * 1.5)).
4
In cell E7, sum total overtime hours, and in F7 sum total gross payroll.
Solution & Formula Breakdown
Let's examine how MIN and MAX separate regular vs overtime hours safely:
1. Regular Hours Capped at 40 with MIN (D2:D5)
=MIN(B2, 40)
- Alex (45 hrs):
MIN(45, 40) = 40 hrs.
- Sarah (38 hrs):
MIN(38, 40) = 38 hrs.
- Carlos (50 hrs):
MIN(50, 40) = 40 hrs.
- Maya (42 hrs):
MIN(42, 40) = 40 hrs.
2. Overtime Hours with MAX (E2:E5)
=MAX(0, B2-40)
- Alex:
MAX(0, 45-40) = 5 hrs.
- Sarah:
MAX(0, 38-40) = MAX(0, -2) = 0 hrs (avoids negative hours!).
- Carlos:
MAX(0, 50-40) = 10 hrs.
- Maya:
MAX(0, 42-40) = 2 hrs.
3. Gross Pay Calculation (F2:F5)
=(D2*C2)+(E2*C2*1.5)
- Alex: (40 × $25) + (5 × $25 × 1.5) = $1,000 + $187.50 = $1,187.50.
- Sarah: (38 × $30) + (0 × $30 × 1.5) = $1,140 + $0 = $1,140.00.
- Carlos: (40 × $20) + (10 × $20 × 1.5) = $800 + $300 = $1,100.00.
- Maya: (40 × $28) + (2 × $28 × 1.5) = $1,120 + $84 = $1,204.00.
4. Payroll Totals (E7 & F7)
- Cell E7 (Total Overtime):
=SUM(E2:E5) → 17 hrs.
- Cell F7 (Total Gross Pay):
=SUM(F2:F5) → $4,631.50.
Using MAX(0, B2-40) prevents employees who worked fewer than 40 hours from showing negative overtime, which would improperly deduct money from their regular pay!