Function Problems

Count Overtime Employees by 30-Minute Interval in Excel

John WilsonJohn Wilson Nov 12, 2026 868 views

Question details

Count the employees with an Overtime status whose overtime start and end times overlap each defined 30-minute interval.

Count Overtime Employees Working Overtime During Time Intervals in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains overtime start times, overtime end times, employee status values, and interval start and end boundaries.
Observed behavior
The required result is the number of overtime records that overlap each interval, including shifts that started before the interval or end after it.
Before you start

Make sure the overtime start and end columns and the interval boundary cells contain real Excel time or date-time values, not text. Decide whether an overtime period ending exactly at an interval start should be excluded or included before choosing the comparison operators.

Solution 1Recommended

Use SUMPRODUCT to count overtime periods that overlap an interval

Use this formula when an employee should be counted whenever any part of their overtime period falls within the interval.

The overlap test is: overtime start is before the interval end, and overtime end is after the interval start. This avoids missing employees who began overtime before the interval started.

1
Set up the interval boundary cells

Enter the interval start time in P3 and the interval end time in Q3. For a 30-minute interval, Q3 can be calculated with =P3+TIME(0,30,0).

2
Enter the overlap-count formula

In the result cell for the interval, enter =SUMPRODUCT(($N$3:$N$18<$Q3)*($O$3:$O$18>$P3)*($H$3:$H$18="Overtime")). Here, N3:N18 contains overtime starts, O3:O18 contains overtime ends, and H3:H18 contains the status.

3
Fill the formula for additional intervals

Create the next interval start and end times in the rows below, then drag the formula down. The fixed employee ranges remain unchanged while P3 and Q3 adjust for each row.

Use SUMPRODUCT to count overtime periods that overlap an interval
How the interval boundaries work: The < and > operators treat intervals as end-exclusive. An overtime period ending exactly at P3 is not counted in that interval, preventing it from being counted twice in adjacent intervals.
Work with time-based formulas in WPS Spreadsheet

Count Overtime Overlaps with SUMPRODUCT in WPS Spreadsheet

WPS Spreadsheet supports SUMPRODUCT formulas for calculating how many overtime records overlap each 30-minute interval. Import your existing workbook, use the same time values and formula structure, and fill the result down for every interval.

  1. 1. Open the overtime workbook: Launch WPS Office, select Spreadsheet, and open the Excel workbook containing the employee overtime table.
  2. 2. Create the interval boundaries: Enter the interval start time in P3 and enter =P3+TIME(0,30,0) in Q3 to create a 30-minute end time.
  3. 3. Add the overlap formula: Select the count cell and enter =SUMPRODUCT(($N$3:$N$18<$Q3)*($O$3:$O$18>$P3)*($H$3:$H$18="Overtime")).
  4. 4. Copy the calculation down: Drag the fill handle down the results column to calculate the overtime count for each subsequent interval.
Use SUMPRODUCT to count overtime periods that overlap a time range.Calculate 30-minute interval boundaries with TIME functions.Open and edit Microsoft Excel .xlsx and .xls workbooks.Use a familiar spreadsheet interface for a smooth transition from Excel.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use the overtime start time before the interval end?

The condition overtime start < interval end identifies records that began before the interval finishes. Combined with overtime end > interval start, it correctly detects any overlap.

How do I prevent an employee from being counted twice at a 30-minute boundary?

Use end-exclusive boundaries: overtime start < interval end and overtime end > interval start. With this method, a record ending exactly when the next interval begins is counted only in the earlier interval if it overlapped it.

Can I count overtime when the shifts run past midnight?

Yes, provided the start and end cells contain full date-time values, such as 1/15/2026 11:30 PM and 1/16/2026 1:30 AM. Time-only values can produce incorrect comparisons across midnight.

What should I change if the Overtime status column uses different text?

Replace "Overtime" in the formula with the exact status used in your worksheet, such as "OT". The text must match the values in H3:H18.

Can this formula count unique employees instead of overtime records?

SUMPRODUCT counts matching rows. If one employee has multiple overtime rows in the same interval, they are counted multiple times; counting unique employee IDs requires a separate unique-count formula or a PivotTable.