Calculate Overtime Pay in Excel Using Job-Specific Rates
Question details
Calculate regular hours and overtime pay using the rate and overtime threshold associated with a selected job.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs to look up job-specific pay settings and apply them to worked hours.
- Observed behavior
- Selecting a job should return its base rate and overtime threshold for the pay calculation.
Gather each job’s hourly base rate and overtime threshold in worked hours, along with the overtime multiplier. Enter worked time as decimal hours; for example, 8 hours and 30 minutes is 8.5 hours.
Look Up Job Settings and Calculate Overtime Pay
Use XLOOKUP to apply the selected job’s rate and threshold to each row of worked hours.
This example treats the threshold as the number of hours worked before overtime starts, not a clock time. It calculates overtime separately for each row; adapt the approach if your pay rules require daily or weekly totals.
In columns J through L, enter the headers Job, Base Rate, and Overtime Threshold. Add one row per job in J2:L10, using an hourly rate and a threshold in decimal hours.
Enter the overtime multiplier in N2. For example, enter 1.5 for overtime paid at one and a half times the base rate.
Enter a job name in B2 that matches column J, and enter the worked hours in C2. Optionally, select B2 and use Data > Data Validation > List to create a job dropdown from the populated cells in column J.
In D2, enter =XLOOKUP(B2,$J$2:$J$10,$K$2:$K$10,""). In E2, enter =XLOOKUP(B2,$J$2:$J$10,$L$2:$L$10,"").
In F2, enter =IF(OR(C2="",D2="",E2=""),"",MIN(C2,E2)) for regular hours. In G2, enter =IF(OR(C2="",D2="",E2=""),"",MAX(0,C2-E2)) for overtime hours. In H2, enter =IF(OR(F2="",G2=""),"",F2*D2+G2*D2*$N$2) for total pay. Copy the formulas down for additional entries.

Calculate Job-Specific Overtime Pay in WPS Spreadsheets
Create a job-rate table in WPS Spreadsheets and use VLOOKUP to apply each job’s rate and overtime threshold to worked hours.
- 1. Set up the lookup table: In WPS Spreadsheets, enter job names, hourly rates, and overtime thresholds in J2:L10. Enter the overtime multiplier, such as 1.5, in N2.
- 2. Enter a job and worked hours: Type a matching job name in B2 and the worked hours as a decimal number in C2.
- 3. Retrieve the job settings: In D2, enter =IFERROR(VLOOKUP(B2,$J$2:$L$10,2,FALSE),""). In E2, enter =IFERROR(VLOOKUP(B2,$J$2:$L$10,3,FALSE),"").
- 4. Calculate overtime and pay: Enter =IF(OR(C2="",D2="",E2=""),"",MIN(C2,E2)) in F2 and =IF(OR(C2="",D2="",E2=""),"",MAX(0,C2-E2)) in G2. In H2, enter =IF(OR(F2="",G2=""),"",F2*D2+G2*D2*$N$2). Copy the formulas down as needed.

Frequently Asked Questions
How do I calculate hours from shift start and end times?
If the start time is in A2 and the end time is in B2, enter =MOD(B2-A2,1)*24 to return decimal hours, including for a shift that crosses midnight. Subtract any unpaid breaks separately.
What if my overtime threshold is a clock time rather than worked hours?
The example formulas use elapsed hours, such as 8 hours, not a clock time such as 5:00 PM. A clock-time threshold requires comparing the shift’s start and end times with that cutoff, including any overnight-shift rules.
Why are the rate and threshold cells blank?
Check that the job in B2 matches an entry in J2:J10 and that its rate and threshold are filled in. The lookup formulas return a blank when the job is not found.
Can I use this approach without XLOOKUP?
Yes. Use VLOOKUP with the job name in the first column of the lookup range, as shown in the WPS steps. This also works in Excel versions that support VLOOKUP but do not have XLOOKUP.




