Count Overnight Employee Shift Overlaps in WPS Spreadsheets
Question details
Count employees whose shifts overlap four-hour schedule periods, including periods and shifts that continue past midnight.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A staff schedule needs employee totals for time blocks such as 11:00 PM to 3:00 AM, with some shift end times occurring on the following day.
- Observed behavior
- Time-only values can make overnight shifts appear to end before they start, causing incorrect employee counts.
Use separate columns for each employee's shift start and shift end. Enter full date-time values for overnight shifts so the end date is the following day, rather than storing both values as times on the same date.
Count employees who work at any time during an overnight period
Use this method when an employee should be counted if any part of their shift overlaps the selected period.
A shift overlaps a period when the shift starts on or before the period end and ends on or after the period start. This works reliably when every start and end value includes a date as well as a time.
In WPS Spreadsheets, place shift start date-times in column B and shift end date-times in column C. Enter the period start in F2 and the period end in G2, then format these cells with Home > Number Format > Date and Time.
For a period from 11:00 PM to 3:00 AM, enter the correct next-day date in G2. For example, if F2 is 1/10/2026 11:00 PM, enter 1/11/2026 3:00 AM in G2.
Select the result cell and enter =SUMPRODUCT((B2:B100<=$G$2)*(C2:C100>=$F$2)). This counts each shift that overlaps the period at any point.
Create separate period start and end cells for each four-hour block, then copy the formula and update the referenced period cells as needed.
Count employees who cover an entire time period
Use this method only when an employee must work for the complete four-hour block to be included.
Convert time-only overnight entries into usable schedule values
Use this approach when the schedule currently stores only times and an overnight end time appears earlier than its start time.
Calculate Overnight Staff Coverage with WPS Spreadsheets
WPS Spreadsheets lets you store shift boundaries as date-time values and use SUMPRODUCT formulas to count employees who overlap or fully cover overnight schedule periods.
- 1. Open the schedule workbook: Open the staff schedule in WPS Spreadsheets and identify the shift date, start time, and end time columns.
- 2. Create date-time boundaries: Combine each shift date with its start and end time, adding one day to an end value when the shift crosses midnight.
- 3. Add period start and end cells: Enter each four-hour period as a start and end date-time, using the next date for an overnight period end.
- 4. Apply an overlap formula: Use =SUMPRODUCT((StartRange<=PeriodEnd)*(EndRange>=PeriodStart)) to count employees working at any time in the period.
Frequently Asked Questions
Why does an overnight shift return the wrong count when I use only times?
A time such as 1:00 AM is numerically earlier than 12:30 PM on the same date. Add the shift date to both values and assign the end time to the following date when it is earlier than the start time.
What formula counts a shift that overlaps a period at all?
Use =SUMPRODUCT((StartRange<=PeriodEnd)*(EndRange>=PeriodStart)). For example, with starts in B2:B100, ends in C2:C100, period start in F2, and period end in G2, use =SUMPRODUCT((B2:B100<=$G$2)*(C2:C100>=$F$2)).
How do I count only employees who work the complete four-hour block?
Use =SUMPRODUCT((StartRange<=PeriodStart)*(EndRange>=PeriodEnd)). With the same example cells, enter =SUMPRODUCT((B2:B100<=$F$2)*(C2:C100>=$G$2)).
How should I enter an 11:00 PM to 3:00 AM period?
Enter the start with its date and 11:00 PM, then enter the end as 3:00 AM on the following date. For example, use 1/10/2026 11:00 PM and 1/11/2026 3:00 AM.
Can I keep start and end times in separate cells from the shift date?
Yes. Create helper date-time values with =ShiftDate+StartTime and =ShiftDate+EndTime+(EndTime<StartTime), then use those helper columns in your counting formulas.
