Forum Discussion
Comparing time slots and events
One way to solve this problem is to use a combination of the SUMPRODUCT and COUNTIFS functions. You can use the SUMPRODUCT function to count the number of times an individual’s ID appears in both sheets. Then, you can use the COUNTIFS function to count the number of events that fall within each time slot for that individual.
Here’s an example formula that you can use in the second sheet to check if any events have happened during any of the time slots for a specific individual:
=SUMPRODUCT((Sheet1!A:A=A2)*(Sheet1!B:B>=B2)*(Sheet1!B:B<=C2))>0
This formula assumes that Sheet1 contains the list of events, with column A containing the person’s ID number and column B containing the date of the event. It also assumes that Sheet2 contains the list of time slots, with column A containing the person’s ID number, column B containing the starting day of the time slot, and column C containing the ending day of the time slot.
The formula checks if the sum of products of three conditions is greater than 0.
The first condition (Sheet1!A:A=A2) checks if the person’s ID number in Sheet1 matches the ID number in cell A2 of Sheet2.
The second condition (Sheet1!B:B>=B2) checks if the date of the event in Sheet1 is greater than or equal to the starting day of the time slot in cell B2 of Sheet2.
The third condition (Sheet1!B:B<=C2) checks if the date of the event in Sheet1 is less than or equal to the ending day of the time slot in cell C2 of Sheet2.
If all three conditions are met for at least one event, then the formula will return TRUE, indicating that at least one event has happened during one of the time slots for that individual.
Otherwise, it will return FALSE.
You can then drag this formula down to fill the rest of column D with TRUE or FALSE values indicating whether any events have happened during any of the time slots for each individual.
I hope this helps! …if not, please insert a file (without sensible data) and explain step by step what you like to accomplish.