Count Overtime Employees by 30-Minute Interval in Excel
Question details
Count the employees with an Overtime status whose overtime start and end times overlap each defined 30-minute interval.

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

Include overtime ending exactly at the interval start when required
Use this variation only when your reporting rules treat an overtime end time equal to the interval start as part of that interval.
Count only overtime that starts within each interval
Use this approach when the report needs to count new overtime starts rather than all employees active during the interval.
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. Open the overtime workbook: Launch WPS Office, select Spreadsheet, and open the Excel workbook containing the employee overtime table.
- 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. 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. Copy the calculation down: Drag the fill handle down the results column to calculate the overtime count for each subsequent interval.

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.




