I've been struggling to find a way to identify pairs of entries for which at least of portion of their time overlaps. To make the challenge more difficult, I need potentially overlapping entries to meet multiple criteria before they are determined to be overlapping.
I have two fields for identifying overlapping entries. One is an "Has Overlap" field which should simply return TRUE or FALSE if an entry shares a time overlap with another entry and meets all the criteria. In the second field, for every pair of entries which overlap and meet the multiple criteria, I need each entry in the pair to be give a common Overlap Group Identification.
I NEED FUNCTIONS WHICH CAN RETURN THE RESULTS WHICH ARE DISPLAYED IN THE EXAMPLE BELOW FOR THE "HAS OVERLAP" FIELD AND THE "OVERLAP GROUP ID" FIELD.
Below is an example of the sheet I'm working on. I have an example which I can attached (if allowed) so it's easier to understand than a big explanation. I've seen some similar posts regarding time overlaps, but none with this multi-criteria requirement. I successfully used the SUMPRODUCT function to identify one overlapping entry in a pair of overlapping entries, but I couldn't get it to identify both entries in the overlapping pair.
Hopefully someone can help me better understand how to accomplish this identification of overlapping times, and better understand the SUMPRODUCT function. If there is a better function to use, I'm all ears.
Thanks for the ideas.
EXAMPLE:
<tbody>
</tbody>
I have two fields for identifying overlapping entries. One is an "Has Overlap" field which should simply return TRUE or FALSE if an entry shares a time overlap with another entry and meets all the criteria. In the second field, for every pair of entries which overlap and meet the multiple criteria, I need each entry in the pair to be give a common Overlap Group Identification.
I NEED FUNCTIONS WHICH CAN RETURN THE RESULTS WHICH ARE DISPLAYED IN THE EXAMPLE BELOW FOR THE "HAS OVERLAP" FIELD AND THE "OVERLAP GROUP ID" FIELD.
Below is an example of the sheet I'm working on. I have an example which I can attached (if allowed) so it's easier to understand than a big explanation. I've seen some similar posts regarding time overlaps, but none with this multi-criteria requirement. I successfully used the SUMPRODUCT function to identify one overlapping entry in a pair of overlapping entries, but I couldn't get it to identify both entries in the overlapping pair.
Hopefully someone can help me better understand how to accomplish this identification of overlapping times, and better understand the SUMPRODUCT function. If there is a better function to use, I'm all ears.
Thanks for the ideas.
EXAMPLE:
| Employee | Customer | Task | Billable Task | Start Date | Start Date Time | End Date Time | Has Overlap | Overlap Group ID | Explanation | ||
| 10 | Employee A | Customer 01 | Task 1 | TRUE | 3/26/2019 | 3/26/19 9:00 AM | 3/26/19 10:00 AM | TRUE | Overlap 01 | "The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 11 since the same employee has two entries which share the same 30 minute timeframe from 9:30 AM to 10:00 AM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps. The entry is given a Overlap Group ID of 1 because it is the first overlapping entry for any employee or customer." | |
| 11 | Employee A | Customer 02 | Task 1 | TRUE | 3/26/2019 | 3/26/19 9:30 AM | 3/26/19 11:00 AM | TRUE |
<tbody> </tbody> | "The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 10 since the same employee has two entries which share the same 30 minute timeframe from 9:30 AM to 10:00 AM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps. This entry is given an Overlap Group ID which matches that in row 10 because it is not the first of the overlapping entries which share this same timeframe for this employee or customer." | |
| 12 | Employee A | Customer 02 | Task 2 | FALSE | 3/26/2019 | 3/26/19 9:30 AM | 3/26/19 11:00 AM | FALSE | Though this entry overlaps two other entries in row 10 and 11, it DOES NOT have a "Billable Task" of TRUE and thus it does not represent an overlapping entry. So the "Has Overlap" field is FALSE. | ||
| 13 | Employee A | Customer 04 | Task 1 | TRUE | 3/26/2019 | 3/26/19 11:00 AM | 3/26/19 12:00 PM | FALSE | The "Has Overlap" field is FALSE because no other entry share this timeframe. | ||
| 14 | Employee A | Customer 05 | Task 1 | TRUE | 3/26/2019 | 3/26/19 12:00 PM | 3/26/19 1:00 PM | TRUE | Overlap 02 | "The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 15 since the same customer has two entries which share the same 30 minute timeframe from 12:30 PM to 1:00 PM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps. The entry is given a Overlap Group ID of 2 because this is the second group of entries which share an overlapping timeframe." | |
| 15 | Employee B | Customer 05 | Task 1 | TRUE | 3/26/2019 | 3/26/19 12:30 PM | 3/26/19 1:30 PM | TRUE | Overlap 02 | "The ""Has Overlap"" field is TRUE because this entry overlaps the entry on row 14 since the same customer has two entries which share the same 30 minute timeframe from 12:30 PM to 1:00 PM. Both overlapping entries have a Billable Task field value of TRUE, so they are recognized as overlaps. This entry is given an Overlap Group ID which matches that in row 14 because it is not the first of the overlapping entries which share this same timeframe for this employee or customer." | |
| 16 | Employee B | Customer 07 | Task 1 | TRUE | 3/26/2019 | 3/26/19 10:00 AM | 3/26/19 11:00 AM | FALSE | The "Has Overlap" field is FALSE because No other entry with a "Billable Task" status share the same customer or same employee, and same timeframe. |
<tbody>
</tbody>
