How to Find Overlapping Times by Name and Date in WPS Office Spreadsheets
Question details
The user needs to determine whether time intervals overlap when records share the same name and date, using formulas such as SUMPRODUCT, SORT, and FILTER.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a schedule or log where rows include a person's name, a date, a start time, and an end time, and needs to identify or review time overlaps for the same person on the same date.
- Observed behavior
- A formula-based result should flag, isolate, or count overlapping time intervals without manually scanning rows.
Confirm that the source data has separate columns for Name, Date, Start Time, and End Time, and that the times are entered as real Excel time values rather than text. If times are stored as text, convert them with TIMEVALUE or re-enter them before applying the formulas.
Flag overlapping time intervals with a SUMPRODUCT helper column
Use this when you want a TRUE/FALSE flag on each row showing whether it overlaps at least one other record with the same name and date.
The SUMPRODUCT formula checks every row in the selected range and counts how many records have the same Name and Date and have a start time before the current row's end time and an end time after the current row's start time. A count greater than 1 means the current row overlaps another row.
Open the workbook in WPS Office Spreadsheets, then click cell E2 or the first empty cell next to the End Time column.
Type =SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2)*($C$2:$C$100<D2)*($D$2:$D$100>C2))>1 and press Enter. Adjust the row range 2:100 to match the actual data range.
Double-click the fill handle in the lower-right corner of cell E2, or drag it down to the last data row.
Select the header row, go to the Data tab, click AutoFilter, open the helper column filter, and select TRUE to display only overlapping records.
Isolate and sort overlapping records with FILTER and SORT
Use this after creating the overlap helper column; it displays overlapping rows in a clean list ordered by start time.
Find Overlapping Times by Name and Date in WPS Office Spreadsheets
Use a SUMPRODUCT helper column or dynamic-array formulas in WPS Office Spreadsheets to flag, filter, and sort overlapping time intervals by name and date without macros or add-ins.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu, select Open, and choose the .xlsx or .xls file that contains the schedule or time log.
- 2. Create the overlap helper column: Insert a new column named Overlap, then enter the SUMPRODUCT formula that compares each row's start and end time with other rows matching the same name and date.
- 3. Filter or isolate overlapping rows: Use AutoFilter to show TRUE rows, or enter a FILTER and SORT formula to return a sorted list of overlapping records.
- 4. Review and save the file: Review the overlapping intervals, then click the Save icon to keep the workbook in Microsoft Excel-compatible .xlsx format.
Frequently Asked Questions
Why does my SUMPRODUCT formula return TRUE for rows that do not visually overlap?
The formula may be including blank cells or comparing times stored as text. Set the range to only the actual data rows and confirm that the Start Time and End Time columns contain real time values. Blank rows can evaluate as midnight and cause false overlaps.
Does WPS Office support Excel's SUMPRODUCT, FILTER, and SORT functions?
Yes. Current versions of WPS Office Spreadsheets support SUMPRODUCT and dynamic array functions such as FILTER and SORT. If FILTER is not available, update WPS Office or use AutoFilter on a SUMPRODUCT helper column.
Can I count how many other records overlap each row instead of showing only TRUE/FALSE?
Yes. Use =SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2)*($C$2:$C$100<D2)*($D$2:$D$100>C2))-1 to return the number of overlapping rows, excluding the current row.
Do I need to sort the data before using the formulas?
No. SUMPRODUCT checks all row combinations regardless of sort order. Sorting with SORT or AutoFilter only helps you review the flagged rows more easily.
What if my schedule crosses midnight?
For shifts that cross midnight, convert the end time to the next day by using =IF(D2<C2,D2+1,D2). This treats end times that are less than the start time as occurring on the following day before applying the overlap formula.
