Calculation Issues

How to Create Excel Timesheet Formulas for Shifts and Weekly Overtime

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to construct Excel formulas to calculate hours worked across different shifts (regular, swing, night) and determine weekly overtime after reaching 40 cumulative hours.

Product: Excel
Device & OS: not provided
Scenario: Tracking employee work hours across various shifts, including overnight schedules, and automating weekly overtime calculations in a timesheet.
Observed behavior: The goal is to accurately categorize hours into specific timeframes without negative time errors, and to trigger overtime calculations once cumulative weekly hours exceed 40.

Before you start

Ensure your time-entry cells are properly formatted as 'Time' or 'Custom (h:mm AM/PM)' in Excel so that time functions calculate the differences correctly.

Solution 1Recommended

Use MAX and MIN Formulas to Calculate Shift Hours

Use a combination of IF, MAX, and MIN functions to correctly allocate hours to specific shifts while automatically preventing negative time errors.

When calculating timesheets, simply subtracting start times from end times can cause calculation errors if the worked hours fall outside a designated shift bracket. Using the MIN and MAX functions ensures only overlapping hours within a given shift are counted.

Remember to multiply the final result by 24 to convert the default Excel time format (which evaluates as a fraction of a day) into standard decimal hours.

Enter the Regular Shift Formula

Assuming the start time is in cell A2 and the end time is in E2, select your regular hours cell and enter: =IF(OR(A2="",E2=""),0,MAX(0,(MIN(E2,TIME(15,0,0))-MAX(A2,TIME(7,0,0))))*24). This calculates the hours worked between 7:00 AM and 3:00 PM.

Enter the Swing Shift Formula

Select your swing shift hours cell and enter: =IF(OR(A2="",E2=""),0,MAX(0,(MIN(E2,TIME(23,0,0))-MAX(A2,TIME(15,0,0))))*24). This calculates the hours worked between 3:00 PM and 11:00 PM.

Apply Formulas to the Timesheet

Click the bottom-right corner of the formula cells and drag the fill handle down to apply these time calculation rules to the rest of the rows in your timesheet.

Calculating Overnight Work: For overnight shifts that cross midnight, you must calculate the portion before midnight and the portion after midnight separately, and then add them together to avoid errors.

Solution 2

Calculate Weekly Overtime After 40 Cumulative Hours

Set up a cumulative hours tracker to trigger overtime calculations once standard weekly hours exceed the 40-hour limit.

Efficient Data Management

Manage Timesheets Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful date and time functions to calculate shift hours and track overtime accurately. Build your timesheets effortlessly with high compatibility and built-in templates.

1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet, or open your existing Excel timesheet document.
2. Format Time Cells: Highlight your entry columns, right-click, choose 'Format Cells', and select the standard 'Time' format to ensure accuracy.
3. Apply the Shift Formulas: Paste the MAX/MIN shift formulas directly into the formula bar and drag the fill handle to apply them across your schedule.

Fully compatible with Microsoft Excel (.xlsx) files and advanced time formats.Supports all essential time functions like TIME, HOUR, MIN, and MAX.Lightweight and runs smoothly for processing complex multi-shift timesheets.Offers free built-in timesheet templates for quick and easy setup.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel time formula return a series of hashtags (######)?

Excel cannot display negative time values by default, resulting in a series of hash marks (######). This usually happens when subtracting an end time that falls past midnight from a start time before midnight. Use the MAX/MIN method to correct this, or format the result cell as General/Number.

How do I calculate simple overnight hours that cross midnight?

For simple overnight calculations, you can use a formula that adds a day if the end time is less than the start time. A common formula is =(End Time - Start Time + (End Time < Start Time)) * 24.

Why do I need to multiply my time formula by 24?

Excel stores time as a fraction of a 24-hour day (for example, 12:00 PM is 0.5). Multiplying by 24 converts this internal fraction into standard decimal hours (e.g., 12.0), making it easier to calculate wages or track cumulative overtime.

Can I use standard IF functions instead of MAX and MIN for shift allocations?

While it is technically possible, nested IF functions for shift hours become extremely complex and difficult to troubleshoot when dealing with overlapping times or partial shifts. Using MAX and MIN is a much cleaner method that automatically prevents negative hour results.

Excel & Data Problems