Formula Errors

Excel Timesheet Formula for Regular Hours and Overtime

Bushra ParveenBushra Parveen Nov 20, 2026 869 views

Question details

Calculate total, regular, and overtime hours in a timesheet using fixed regular-shift hours, including overnight work.

Calculate Regular Hours and Overtime in an Excel Timesheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the timesheet

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.

2
Calculate total hours

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.

3
Calculate regular hours

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))).

4
Calculate overtime

In E2, enter =C2-D2 to subtract regular hours from total hours. Copy C2:E2 down for additional shifts.

5
Format the results

Select C:E, open Format Cells, choose Custom, and enter [h]:mm as the format. This also displays totals over 24 hours correctly.

Calculate Hours Against a Fixed Regular-Shift Period
Check an overnight shift: For a shift from 19:00 to 09:00, the formulas return 14:00 total, 2:00 regular, and 12:00 overtime. Identical start and end times return zero, not 24 hours.
Calculate timesheets in WPS

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. 1. Open the timesheet: Open WPS Spreadsheet and create a blank workbook or open an Excel timesheet.
  2. 2. Enter shift times: Enter start times in column A and end times in column B, beginning in row 2.
  3. 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. 4. Format and save: Select columns C:E, use Format Cells > Custom to apply [h]:mm, and save the workbook as an XLSX file.
Calculate regular and overtime hours for overnight shiftsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and cell formattingWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.