Calculation Issues

Calculate Regular and Overtime Hours in Excel Timesheets

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to calculate daily regular working hours up to an 8-hour limit and track any additional hours as overtime in an Excel timesheet.

Product: Excel
Device & OS: not provided
Scenario: Tracking employee timesheets or personal working hours to determine payroll or compensation.
Observed behavior: The goal is to automatically separate total worked hours into regular hours (capped at 8 per day) and overtime hours (any duration exceeding the 8-hour limit).

Before you start

Ensure your start and end times in the timesheet are formatted correctly as Time (e.g., hh:mm AM/PM) so the formulas can accurately calculate the differences.

Solution 1Recommended

Use Array Formulas for Regular and Overtime Hours

Use array formulas to process multiple rows of start and end times at once, splitting them by the standard 8-hour workday limit.

These formulas calculate the total duration by subtracting the start time from the end time. They also handle overnight shifts using the MOD function and correctly cap regular hours at 8 per day.

Identify your data ranges

Determine the cells containing your shift data. For this example, assume C5:C15 contains Start Times and D5:D15 contains End Times.

Enter the formula for Regular Hours

Select the cell where you want the total regular hours. Enter the formula: =24*SUM(IF(MOD(D5:D15-C5:C15,1)>TIME(8,0,0),TIME(8,0,0),MOD(D5:D15-C5:C15,1)))

Enter the formula for Overtime Hours

Select the cell for total overtime hours and enter the formula: =24*SUM(IF(MOD(D5:D15-C5:C15,1)>TIME(8,0,0),MOD(D5:D15-C5:C15,1)-TIME(8,0,0),0))

Format the results as Number

Select both result cells, right-click and choose Format Cells. Select 'Number' from the category list to display the total hours as a decimal.

Confirm as an array formula (Older versions only)

If you are using a version older than Microsoft 365, Excel 2024, or Excel 2021, you must confirm both formulas by pressing Ctrl+Shift+Enter instead of just Enter.

Array Formula Requirement: If your regular hours calculation returns an unexpected result (such as not returning exactly 24 for three 8-hour shifts), double-check that you have confirmed the formula with Ctrl+Shift+Enter.

Track Working Hours Easily

Calculate Timesheets Easily in WPS Spreadsheet

You can perform advanced time tracking, including regular and overtime hour calculations, directly in WPS Spreadsheet using the exact same formulas.

1. Open your timesheet in WPS: Launch WPS Spreadsheet and open your existing timesheet document.
2. Format your time columns: Enter the start and end times in your columns and ensure they are formatted as Time via the Format Cells menu.
3. Input the calculation formulas: Paste the regular and overtime formulas into your designated total cells. Remember to use Ctrl+Shift+Enter to apply the array formula.
4. Format totals as Number: Right-click the formula result cells, click 'Format Cells', and select 'Number' to view the total decimal hours correctly.

Fully compatible with Microsoft Excel formulas and array functionsEasy-to-use cell formatting for timesheets and numeric valuesFree to use for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my regular hours formula returning an error or wrong number?

You may need to execute it as an array formula. In older versions of Excel or WPS Office, you must press Ctrl+Shift+Enter after pasting the formula instead of just Enter. Also, ensure the target cell is formatted as a Number, not a Time.

How does the MOD function work in this time calculation?

The MOD function (e.g., MOD(End-Start, 1)) ensures that the time difference is calculated correctly even if a shift crosses midnight. It handles negative time values by wrapping them around a standard 24-hour clock.

Can I change the regular hours limit from 8 hours to something else?

Yes. You can replace TIME(8,0,0) in both formulas with your specific daily limit, such as TIME(7,30,0) for a 7.5-hour workday.

Why do I need to multiply the entire formula by 24?

Spreadsheet software stores times as fractions of a 24-hour day. Multiplying the final result by 24 converts the fractional daily value into standard decimal hours (e.g., 8.5 hours instead of 0.354 days), making it easier to calculate payroll.

Excel & Data Problems