Calculate Regular, Overtime, and Sunday Double-Time Hours in Excel
Question details
Calculate daily regular, overtime, and Sunday double-time hours in an Excel timesheet after deducting lunch.

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

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. 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. 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. Calculate regular hours: In E2, enter =IF(D2="","",IF(WEEKDAY(A2,2)=7,0,MIN(D2,8))).
- 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. 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.

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.




