Formula Errors

How to Calculate Overtime Hours Above 40 Using Excel Formulas

Maira Mehtab Sep 28, 2026 871 views

Question details

The user needs a formula to automatically separate total hours worked into regular hours and overtime hours (anything above 40) on a payroll worksheet.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Managing an hourly payroll worksheet where overtime pay applies to any accumulated hours exceeding a 40-hour limit.
Observed behavior: The user wants the worksheet to automatically populate the overtime column with the correct additional hours once the 40-hour threshold is crossed.

Before you start

Ensure your worksheet has dedicated columns for Total Hours, Regular Hours, and Overtime Hours, and verify that the hours are formatted as standard numbers rather than time values.

Solution 1Recommended

Use the IF Function to Calculate Overtime

Apply a simple IF formula to check if the total hours exceed 40 and calculate the overtime difference automatically.

The IF function is perfect for conditional payroll calculations. It evaluates whether the total hours are greater than 40. If they are, it subtracts 40 from the total to find the overtime. If they are not, it returns 0.

Select the Overtime cell

Click on the cell in your worksheet where you want the overtime hours to appear (for example, E6).

Enter the IF formula

Type the formula =IF(C6>40,C6-40,0) (assuming your total accumulated hours are in cell C6) and press Enter.

Apply to other rows

Click and drag the fill handle at the bottom-right corner of the cell to copy the formula down to other employee rows in your payroll sheet.

Cumulative Weekly Total: If the 40-hour limit applies to a cumulative weekly total rather than a single entry, you must calculate a running weekly total first and derive overtime from that cumulative total rather than evaluating each daily row independently.

Solution 2

Calculate the Regular Hours (Up to 40)

Use a MIN function to ensure the regular hours column accurately displays up to 40 hours without exceeding it.

Manage Payroll Easily

Calculate Payroll and Overtime Effortlessly in WPS Office

WPS Spreadsheet provides robust formula capabilities, including IF and MIN functions, to help you automate payroll calculations, manage overtime, and keep your finances organized.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing payroll spreadsheet.
2. Enter your data: Input your employees' total worked hours into a designated column.
3. Apply the formula: Use =IF(C6>40,C6-40,0) in the overtime column to automatically calculate hours above 40.

100% compatible with Microsoft Excel formulas and formats.Built-in templates for payroll and attendance tracking.Lightweight software that runs smoothly on any device.Free to download and use for your daily spreadsheet needs.

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate overtime if the standard workweek is 35 hours instead of 40?

Simply replace the number 40 in the formula with 35. The updated formula will be =IF(C6>35,C6-35,0).

Why is my formula returning an error or incorrect value?

Check that your total hours are entered as standard numbers (e.g., 42) rather than a specific time format (like 42:00). If using a time format, you must multiply the values by 24 to convert them to decimal hours before applying the overtime calculation.

How can I automatically calculate the overtime pay amount?

Once you have the overtime hours calculated in a cell (e.g., E6), multiply that cell by the employee's overtime rate (e.g., =E6*25.50) in a new 'Overtime Pay' column.

Can I use this formula for daily overtime over 8 hours?

Yes, if you calculate overtime daily rather than weekly, you can use the exact same logic. Use =IF(B2>8,B2-8,0) where B2 represents the hours worked in a single day.

Excel & Data Problems