Formula Errors

How to Build an Excel Timesheet Formula for Custom Week Start Days

John Wilson Oct 19, 2026 869 views

Question details

The user needs an Excel formula to calculate straight time and overtime hours based on a custom work week start day.

How to Build an Excel Timesheet Formula for Custom Week Start Days

Product: Excel
Device & OS: not provided
Scenario: Creating a timesheet that accommodates multiple clients who use different days (e.g., Saturday, Sunday, Monday) to start their work week, ensuring the 40-hour overtime threshold resets correctly.
Observed behavior: The week number needs to be dynamically adjusted so that overtime calculations reset accurately depending on the client's chosen week start day.

Before you start

Ensure your timesheet includes designated cells containing the client's selected week-start day, the date being evaluated, and the corresponding day of the week.

Solution 1Recommended

Use Nested IF and WEEKNUM Functions

Adjust the week number using a logical formula to correctly align the 40-hour overtime threshold with the client's specific week start day.

By dynamically adjusting the week number using IF, AND, and WEEKNUM functions, you can force the overtime calculation to reset based on a custom starting day like Monday, Saturday, or Sunday. This ensures accurate cumulative hour tracking.

Set up reference cells

Ensure your timesheet layout has dedicated cells for the client's preferred week-start day (e.g., cell AK16), the date being evaluated (e.g., C18), and the current text day of the week (e.g., AI18).

Enter the dynamic week number formula

Click on the cell where you want the adjusted week number to appear and input the following formula: =IF($C18=" "," ",IF(AND($AK$16="Mon",$AI18="Sun"),WEEKNUM($C18)-1,IF(AND($AK$16="Sat",$AI18="Sat"),WEEKNUM($C18)+1,WEEKNUM($C18))))

Apply to overtime calculations

Press Enter. Use this dynamically adjusted week number column as the basis for your straight-time and overtime logic to reset the 40-hour clock accordingly.

Cell References: You will need to adjust the cell references in the formula ($AK$16, $C18, $AI18) to match the actual rows and columns of your timesheet.

Easily Manage Custom Timesheets with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and date functions, allowing you to seamlessly calculate custom overtime hours and manage complex timesheets just like you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing timesheet or create a new blank workbook.
2. Enter the formula: Select the target cell and input the nested IF and WEEKNUM formula to track your dynamic week numbers.
3. Drag to fill: Use the fill handle at the bottom right corner of the cell to apply the formula down the entire timesheet column.

100% compatible with Excel formulas like IF, AND, and WEEKNUM.Lightweight and runs smoothly even with complex timesheet calculations.Free built-in templates to kickstart your professional timesheet design.

microsoft office alternative - wps office

Frequently Asked Questions

What does the WEEKNUM function do in Excel?

The WEEKNUM function returns the week number of a specific date in the year, ranging from 1 to 52 (or 53). You can optionally specify a return_type argument to set which day the week begins on.

Can I use a built-in return type in WEEKNUM instead of a complex IF formula?

Yes, for static week starts, WEEKNUM(date, return_type) supports days like Monday (return_type 2) or Sunday (return_type 1). However, custom logic using IF functions is necessary when the week start day must change dynamically based on a value referenced from another cell.

Why is my overtime calculating incorrectly on custom days?

Overtime usually relies on a 40-hour weekly threshold. If the calculated week number doesn't reset on the exact start day for the client, cumulative hours carry over to the wrong days, causing straight time to be improperly flagged as overtime.

Excel & Data Problems