Function Problems

Calculate Overtime Pay in Excel Using Job-Specific Rates

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 869 views

Question details

Calculate regular hours and overtime pay using the rate and overtime threshold associated with a selected job.

Calculate Overtime Pay in Excel Using Job-Specific Rates
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the job table

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.

2
Set the overtime multiplier

Enter the overtime multiplier in N2. For example, enter 1.5 for overtime paid at one and a half times the base rate.

3
Enter the job and hours

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.

4
Look up the rate and threshold

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,"").

5
Calculate hours and pay

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.

Look Up Job Settings and Calculate Overtime Pay
Check your pay rules: The formulas assume one overtime multiplier and nonnegative worked hours. Confirm the applicable overtime rules and whether the threshold applies per shift, day, or week.
Calculate overtime in WPS Spreadsheets

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. 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. 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. 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. 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.
Automatically apply a job-specific rate and overtime thresholdOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and formattingWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.