Calculate Regular, Overtime, Diving, and Per Diem Payroll in WPS Spreadsheets
Question details
Calculate total payroll from regular hours, overtime hours over 40, diving hours, double-time hours, and per diem hours or units with different rates.
- Product
- WPS Spreadsheets payroll worksheet
- Device & OS
- not provided
- Scenario
- The user needs one worksheet formula that calculates an employee's total pay using rate and hour values stored in the same row.
- Observed behavior
- The total must include base pay for all regular hours, a 50% overtime premium for hours above 40, and separate diving, double-time, and per diem amounts.
Confirm that the referenced cells contain numbers and that column G holds total regular and overtime hours for the pay period. Check that columns D, E, and F contain the intended base, diving, and per diem rates before filling the formula down.
Use one payroll formula with an overtime premium after 40 hours
Use this formula when all hours in column G receive the base rate and only hours above 40 receive an additional 50% overtime premium.
This approach pays every hour in G at the base rate in D, then adds half of the base rate only to hours exceeding 40. It also adds diving pay, double-time pay, and per diem using the values in columns H through J.
Click the total payroll cell for the employee row, such as K5, where you want the calculated pay amount to appear.
Type or paste =$D5*$G5+$D5*0.5*MAX(0,$G5-40)+$H5*$E5+$I5*($D5*2)+$J5*$F5 and press Enter.
Confirm that D is the base hourly rate, E is the diving rate, F is the per diem rate, G is total hours, H is diving hours, I is double-time hours, and J is per diem hours or units.
Select the formula cell and drag the fill handle down, or copy the cell and use Home > Paste to apply it to the remaining employee rows.
Use an IF formula when you want separate under-40 and over-40 logic
Use this version if you prefer to see the standard-hours and overtime-hours calculations as separate branches.
Build a Multi-Rate Payroll Formula in WPS Spreadsheets
WPS Spreadsheets lets you calculate payroll with familiar Excel-compatible formulas. Enter the combined formula once, use MAX to apply overtime premiums only after 40 hours, and fill it down for every employee.
- 1. Open the payroll workbook: Open your payroll sheet in WPS Spreadsheets and locate the first employee row.
- 2. Choose the total-pay column: Click the first blank cell in the Total Pay column, such as K5.
- 3. Add the payroll calculation: Enter =$D5*$G5+$D5*0.5*MAX(0,$G5-40)+$H5*$E5+$I5*($D5*2)+$J5*$F5, then press Enter.
- 4. Apply the formula to all employees: Drag the fill handle down from the calculated cell to copy the formula through the rest of the payroll table.
Frequently Asked Questions
Why does the formula add only 0.5 times the base rate for overtime hours?
The formula already pays all hours in G at the base rate with $D5*$G5. Adding $D5*0.5*MAX(0,$G5-40) increases only hours above 40 from 1.0 times to 1.5 times the base rate.
What happens when an employee works exactly 40 hours?
MAX(0,$G5-40) returns 0 when G5 is 40, so the employee receives only base pay for those 40 hours plus any diving, double-time, and per diem amounts.
How do I calculate overtime if it starts after a different number of hours?
Replace 40 in MAX(0,$G5-40) with your overtime threshold. For example, use MAX(0,$G5-35) when overtime begins after 35 hours.
Can I use this payroll formula in an Excel workbook opened in WPS Spreadsheets?
Yes. WPS Spreadsheets supports the MAX and IF functions used by this formula and can work with Microsoft Excel .xlsx files.
Why are dollar signs used in the cell references?
The dollar signs lock the referenced columns when you fill the formula down. The row number remains relative, so D5 becomes D6, D7, and so on for each employee.
