How to Check Whether Events Occur Within Time Slots in WPS Office Spreadsheets
Question details
The user needs a formula that returns TRUE or FALSE when a person has at least one event date inside one of that person's time slots, evaluating multiple events and multiple slots rather than only the first match.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has one sheet with person IDs and event dates, and another time-slot sheet with person IDs, start dates, and end dates. The user wants to flag each time slot that contains at least one matching event.
- Observed behavior
- Standard lookup functions may return only the first match, so they do not reliably show whether any event falls within the time slot.
Make sure the event dates and the slot start/end dates are real date values rather than text, and confirm the sheet name and range references match your workbook before entering the formula.
Use SUMPRODUCT to Check Any Event Within a Time Slot
Use this formula when you need a TRUE/FALSE result for each time-slot row and want to evaluate all matching events for the same person.
SUMPRODUCT multiplies the three logical conditions for each row and totals the results. If the total is greater than zero, at least one event matches the person and falls within the slot.
Open your workbook in WPS Office Spreadsheets and select the worksheet that contains person IDs, start dates, and end dates.
In an empty helper column on the time-slot sheet, enter =SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000>=B2)*(Sheet1!$B$2:$B$1000<=C2))>0. Replace Sheet1 with the sheet that holds the event data, and adjust A2:C2 to your first row.
Drag the fill handle down so each time-slot row is evaluated against the bounded event range.
Use COUNTIFS for a Simpler Bounded-Range Check
Use COUNTIFS when you want a shorter formula that returns the count of matching events, then compare it to zero.
Check Time-Slot Events in WPS Office Spreadsheets
WPS Office Spreadsheets can handle the same SUMPRODUCT and COUNTIFS formulas as Excel, so you can open the workbook, enter the formula, and fill it down to see TRUE or FALSE for every time slot without leaving WPS.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets, click Menu > Open, and select the workbook containing the event and time-slot sheets.
- 2. Go to the time-slot worksheet: Click the time-slot sheet tab at the bottom of the window and select the first result cell where you want the TRUE/FALSE flag.
- 3. Enter the SUMPRODUCT formula: Type =SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000>=B2)*(Sheet1!$B$2:$B$1000<=C2))>0 and press Enter. Replace Sheet1 and range references as needed.
- 4. Copy the formula down: Drag the fill handle from the result cell down through all time-slot rows to evaluate each slot.
- 5. Verify results: Check that TRUE appears for slots with at least one matching event and FALSE appears when no event date falls within the range.
Frequently Asked Questions
Can this formula work when event dates are on a different sheet?
Yes. Change Sheet1 in the formula to the exact name of the sheet that contains the event data, and keep the person ID and date column references aligned with that sheet.
Why does the formula return FALSE even though the date looks correct?
The cell probably contains a text date instead of a real date value. In WPS Office Spreadsheets, select the date cells, open Format Cells, and confirm they use a Date format; then re-enter or convert text dates using DATEVALUE if needed.
How can I make the end date inclusive?
Use <=C2 instead of <C2 in the date comparison. If the end date should be exclusive, use <C2.
Is COUNTIFS a better option than SUMPRODUCT?
Both work for this check. COUNTIFS is shorter and often faster with bounded ranges; SUMPRODUCT is useful when you need to combine row-wise conditions and compare the result to zero.
Can I avoid whole-column references for better performance?
Yes. Use bounded ranges such as $A$2:$A$1000 instead of A:A so the formula only evaluates the used rows and remains faster.
