Calculation Issues

How to Calculate Weekly Straight-Time and Overtime Hours in Excel

Camila Milosovich Oct 19, 2026 869 views

Question details

The user needs a dynamic formula to automatically calculate straight-time and overtime hours in an Excel timesheet that spans multiple weeks.

How to Calculate Weekly Straight-Time and Overtime Hours in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing an event-based timesheet across multiple sheets where the start date can be any day of the week, weekends automatically count as overtime, and straight-time is capped at 40 hours per week.
Observed behavior: The user requires a solution that correctly tracks weekly hours and allocates overtime dynamically without manually adjusting the range for each new week or sheet.

Before you start

Ensure your timesheet layout is consistent across all sheets, with dates listed consecutively in one column and total daily hours in another.

Solution 1Recommended

Use Dynamic LET and SUMIFS Formulas for Timesheet Calculations

Implement a combination of LET, SUMIFS, and WEEKDAY functions to automatically identify the current workweek and allocate straight-time and overtime hours based on a 40-hour threshold.

This dynamic approach eliminates the need to manually adjust weekly cell ranges. It automatically detects the Sunday that starts the current workweek, sums the prior hours worked in that week, forces Saturday and Sunday hours to zero straight-time (making them overtime), and limits the weekday straight-time to a maximum of 40 hours.

Set up the Straight-Time Formula

Assuming your dates are in column C (starting at C18) and your daily total hours are in column H (starting at H18), click on the first cell in your Straight-Time column (e.g., I18) and enter the following formula: =LET(d,C18,h,H18,w,d-WEEKDAY(d,1)+1,prior,SUMIFS($H:$H,$C:$C,">="&w,$C:$C,"<"&d),IF(OR(h=0,WEEKDAY(d,1)=1,WEEKDAY(d,1)=7),0,MAX(0,MIN(h,40-prior))))

Set up the Overtime Formula

In the adjacent Overtime column for the same row (e.g., J18), subtract the straight-time from the total hours by entering: =H18-I18

Apply Formulas Down the Sheet

Select both formula cells (I18 and J18) and drag the fill handle down to apply these calculations for the rest of the days on your sheet. Because the formulas rely on dynamic dates rather than fixed weekly rows, you can safely copy this setup to all your event-based sheets.

Use Dynamic LET and SUMIFS Formulas for Timesheet Calculations

Formula Compatibility: The LET function is available in newer versions of Excel and WPS Office. It significantly simplifies complex logic and improves calculation speed for large timesheets.

WPS Spreadsheet Solution

Manage Complex Timesheets Efficiently with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like LET, SUMIFS, and WEEKDAY. You can seamlessly track straight-time and overtime hours across multiple event-based sheets with high performance and exact format compatibility.

1. Open Your Timesheet: Launch WPS Spreadsheet and open your existing timesheet workbook.
2. Apply the Dynamic Formula: Navigate to the Straight-Time column and paste the LET formula. WPS will automatically process the logic for the 40-hour limit and weekend identification.
3. Copy Across Worksheets: Calculate the Overtime column, then simply copy and paste your columns into your other event-based sheets. WPS Spreadsheet handles cross-sheet dynamic calculations effortlessly.

Fully compatible with Microsoft Excel (.xlsx) formulas and formattingSupports modern functions including LET, MAX, and SUMIFS for advanced logicFree, lightweight, and fast processing for workbooks with 100+ sheetsFamiliar interface makes cross-sheet referencing easy and intuitive

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the workweek start day to Monday instead of Sunday?

Yes, you can modify the WEEKDAY function within the formula. Changing WEEKDAY(d, 1) to WEEKDAY(d, 2) will change the return type so that Monday is treated as the first day of the workweek.

What if my timesheet columns are different from the example?

You only need to update the initial variable references in the LET function. Change C18 to the cell containing your first date, and H18 to the cell containing your daily total hours.

Do these formulas work across multiple sheets automatically?

The formulas calculate locally on each sheet based on that specific sheet's date values. Because they dynamically identify the week using the date rather than row placement, you can copy the exact same formula structure to any sheet that shares the same column layout.

Why does the formula assign 0 straight-time hours on weekends?

The logical argument OR(h=0, WEEKDAY(d,1)=1, WEEKDAY(d,1)=7) specifically checks if the day is Sunday (1) or Saturday (7). If true, it forces the straight-time to 0, shifting all recorded hours for those days into the overtime calculation.

Excel & Data Problems