Calculation Issues

Calculate Regular, Overtime, and Sunday Double-Time Hours in Excel

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Calculate daily regular, overtime, and Sunday double-time hours in an Excel timesheet after deducting lunch.

Calculate Regular, Overtime, and Sunday Double-Time Hours in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A timesheet needs to classify net daily work hours using an eight-hour regular-time threshold and a separate Sunday double-time category.
Observed behavior
The desired result is separate totals for regular, overtime, and Sunday double-time hours based on the recorded date and net hours worked.
Before you start

Confirm that each row represents one shift, dates and times are entered as Excel date and time values, and every shift has a 30-minute unpaid lunch. The formulas below classify the entire shift by its start date, assume shifts are shorter than 24 hours, and do not round partial hours.

Solution 1Recommended

Use separate Excel formulas for each pay category

Use this layout to calculate decimal hours with daily overtime after eight net hours and all Sunday hours in the double-time column.

Under these assumptions, a non-Sunday shift with 8.5 elapsed hours produces 8 regular hours after lunch; a 12.5-hour shift produces 8 regular and 4 overtime hours. Sunday hours appear only in the double-time column.

These are classification formulas, not a determination of applicable pay rules. Confirm the lunch deduction, Sunday treatment, rounding, and vacation or sick-time policy before using them for payroll.

1
Set up the timesheet

In row 1, enter Date, Start, End, Net Hours, Regular, Overtime, and Sunday Double Time in columns A through G. Enter each shift's start date in column A and its start and end times in columns B and C.

2
Calculate net hours

In D2, enter =IF(OR(A2="",B2="",C2=""),"",MAX(0,MOD(C2-B2,1)*24-0.5)). This deducts 30 minutes and handles a shift ending after midnight.

3
Calculate regular hours

In E2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,0,MIN(D2,8))). This limits non-Sunday regular hours to eight.

4
Calculate overtime and Sunday hours

In F2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,0,MAX(D2-8,0))). In G2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,D2,0)).

5
Fill and check the formulas

Select D2:G2 and drag the fill handle down for the remaining shifts. Format columns D:G as Number with the desired decimal places, then check sample weekday and Sunday shifts.

Use separate Excel formulas for each pay category
Overnight shifts and leave: An overnight shift is classified by the date in column A, even if it crosses into or out of Sunday. Record vacation and sick time separately unless your pay policy specifies how to include them.
Calculate hours in WPS Spreadsheets

Build the same timesheet in WPS Spreadsheets

Use WPS Spreadsheets to enter shifts and calculate net, regular, overtime, and Sunday double-time hours with the same formulas.

  1. 1. Create the columns: Open WPS Spreadsheets and label columns A:G as Date, Start, End, Net Hours, Regular, Overtime, and Sunday Double Time. Enter a shift date and its start and end times in row 2.
  2. 2. Deduct lunch: In D2, enter =IF(OR(A2="",B2="",C2=""),"",MAX(0,MOD(C2-B2,1)*24-0.5)) to calculate decimal hours after a 30-minute lunch.
  3. 3. Calculate regular hours: In E2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,0,MIN(D2,8))).
  4. 4. Calculate the other categories: In F2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,0,MAX(D2-8,0))). In G2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,D2,0)).
  5. 5. Fill and save: Drag the fill handle from D2:G2 down the timesheet, format those columns as numbers, and use File > Save As to save the workbook as an Excel XLSX file.
Separate regular, overtime, and Sunday double-time hoursOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill handlesWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the eight-hour threshold apply before or after lunch?

In these formulas, it applies after the 30-minute lunch deduction. For example, 8.5 elapsed hours yields 8 net regular hours on a non-Sunday.

Are Sunday hours also counted as regular or overtime hours?

No. When the date in column A is Sunday, the formulas put all net hours in column G and return zero in the regular and overtime columns.

How are shifts that cross midnight handled?

The MOD formula calculates elapsed time across midnight for shifts shorter than 24 hours. The entire shift is classified using its start date in column A; split the shift into separate rows if your policy requires hours on each date to be classified separately.

Can I change the lunch deduction or round partial hours?

Change 0.5 in the D2 formula to the applicable deduction in decimal hours. To round partial hours, first confirm your policy and then apply an appropriate rounding formula to the net-hours calculation.

How should I enter vacation or sick time?

Record leave in separate columns or rows according to your pay policy. The shift formulas assume start and end times represent work and automatically deduct lunch.