Excel Timesheet Formula for Regular Hours and Overtime
Question details
Calculate total, regular, and overtime hours in a timesheet using fixed regular-shift hours, including overnight work.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Employees can start and end work outside a fixed regular period of 8:00 AM to 8:00 PM.
- Observed behavior
- The timesheet should count work within the regular period as regular hours and work outside it as overtime, even when a shift crosses midnight.
Enter start and end times as Excel time values, such as 19:00 and 09:00. The formulas below assume shifts are shorter than 24 hours, have no unpaid breaks, and use the same 8:00 AM–8:00 PM regular period every day.
Calculate Hours Against a Fixed Regular-Shift Period
Use these formulas when regular hours depend on the time of day rather than a fixed number of hours worked.
The regular-hours formula adds the overlap with the 8:00 AM–8:00 PM period on the start day and, if applicable, the following day. All remaining time is counted as overtime.
In Excel, enter Start in A1, End in B1, Total in C1, Regular in D1, and Overtime in E1. Enter a shift's start and end times in A2 and B2.
In C2, enter =MOD(B2-A2,1). This calculates the elapsed time when the end time is on the same day or the following day.
In D2, enter =MAX(0,MIN(A2+C2,TIME(20,0,0))-MAX(A2,TIME(8,0,0)))+MAX(0,MIN(A2+C2,1+TIME(20,0,0))-MAX(A2,1+TIME(8,0,0))).
In E2, enter =C2-D2 to subtract regular hours from total hours. Copy C2:E2 down for additional shifts.
Select C:E, open Format Cells, choose Custom, and enter [h]:mm as the format. This also displays totals over 24 hours correctly.

Calculate Regular and Overtime Hours in WPS Spreadsheet
Use WPS Spreadsheet to enter the same time formulas, separate fixed-period regular hours from overtime, and format the results as elapsed hours.
- 1. Open the timesheet: Open WPS Spreadsheet and create a blank workbook or open an Excel timesheet.
- 2. Enter shift times: Enter start times in column A and end times in column B, beginning in row 2.
- 3. Add the formulas: Enter the total, regular, and overtime formulas from the solution above in C2, D2, and E2, then copy them down.
- 4. Format and save: Select columns C:E, use Format Cells > Custom to apply [h]:mm, and save the workbook as an XLSX file.

Frequently Asked Questions
How does the formula handle a shift that crosses midnight?
The formula =MOD(B2-A2,1) treats an end time earlier than the start time as occurring the next day. The regular-hours formula checks the regular period on both days.
How do I change the regular shift from 8:00 AM–8:00 PM?
In the D2 formula, replace both TIME(8,0,0) values with the new start time and both TIME(20,0,0) values with the new end time. The formula assumes the regular period starts and ends on the same day.
Why does a weekly total show the wrong number of hours?
Select the total cell, open Format Cells > Custom, and use [h]:mm. A standard time format can wrap the displayed hours after 24.
Can I use these formulas for a 24-hour shift or deduct unpaid breaks?
Not as written. Identical start and end times are treated as zero hours, and breaks are not deducted. Use dated start and end values for shifts of 24 hours or more, and account for break times separately.




