Function Problems

How to Cap Hours at 40 in Excel (Return 40 or the Smaller Value)

Guest Writer Oct 1, 2026 868 views

Question details

The user needs a formula to cap regular weekly hours at 40 in a payroll worksheet, returning the actual hours if they are under 40.

How to Cap Hours at 40 or Return the Smaller Value in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating regular and overtime hours for payroll, where weekly regular hours must not exceed a limit of 40.
Observed behavior: The user needs to restrict the output to a maximum of 40. When using time formats like [h]:mm, the result incorrectly displays as a small decimal (e.g., 1.6666667) because the time value is not properly converted to standard hours.

Before you start

Before applying the formula, verify whether your source cells contain actual time values (formatted as [h]:mm) or standard decimal numbers, as this dictates whether you need a time conversion in your formula.

Solution 1Recommended

Use the MIN Function with Time Conversion

Use the MIN function combined with a time-to-decimal conversion to accurately cap weekly hours at 40 when your data is in a time format.

Excel stores time as a fraction of a 24-hour day. To convert a time value into standard decimal hours for payroll calculations, you must multiply the cell value by 24.

Select the destination cell

Click on the cell where you want the capped regular hours to be displayed.

Enter the MIN formula

Type the formula =MIN(24*I16, 40) into the formula bar, replacing I16 with the cell reference that contains your total weekly hours.

Format the result cell

Right-click the result cell, select 'Format Cells', choose the 'Number' category, and set it to 2 decimal places to display the hours correctly.

Use the MIN Function with Time Conversion

Formatting Tip: If you see a result like 1.6666667, it means the calculation is working but the cell format needs to be changed from [h]:mm to Number.

Solution 2

Use the MIN Function for Decimal Hours

If your total hours are already entered as standard decimal numbers (e.g., 38.5), you can use the MIN function directly without multiplying by 24.

Efficient Data Management

Easily Calculate Payroll and Cap Hours with WPS Spreadsheet

WPS Spreadsheet provides all the advanced functions, including MIN, MAX, and robust time formatting tools, to help you calculate payroll accurately and efficiently.

1. Open your payroll workbook: Launch WPS Spreadsheet and open the file containing your timesheet data.
2. Apply the MIN formula: Click on the target cell and input =MIN(24*I16, 40) to cap the hours.
3. Calculate the result: Press Enter to instantly calculate the capped regular hours.
4. Adjust number formatting: Right-click the cell, choose 'Format Cells', and set it to a Number format with 2 decimal places.

Fully compatible with Microsoft Excel formulas, time formats, and payroll templates.User-friendly interface for quick cell formatting and data analysis.Built-in advanced functions for handling complex time-to-decimal conversions.Lightweight software that processes large payroll datasets without lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my hour calculation show a strange decimal like 1.6666667?

Excel stores time as a fraction of a day. For example, 40 hours is stored as 40/24, which equals 1.6666667. To convert a time value into standard decimal hours, you must multiply the cell reference by 24 and format the result cell as a Number.

How do I calculate the overtime hours over 40?

You can use the MAX function. For standard decimal hours, use the formula =MAX(I16-40, 0). If you are calculating from a time value formatted cell, use =MAX((24*I16)-40, 0).

Can I use the IF function instead of MIN to cap hours?

Yes, you can use the IF function with the formula =IF((24*I16)>40, 40, 24*I16). However, using the MIN function is generally preferred as it makes the formula much shorter and easier to read.

Excel & Data Problems