Identify Overlapping Times with Multiple Criteria

[DMDGUSA]

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:
[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Employee[/TD]
[TD]Customer[/TD]
[TD]Task[/TD]
[TD]Billable Task[/TD]
[TD]Start Date[/TD]
[TD]Start Date Time[/TD]
[TD]End Date Time[/TD]
[TD]Has Overlap[/TD]
[TD]Overlap Group ID[/TD]
[TD]Explanation[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Employee A[/TD]
[TD]Customer 01[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 9:00 AM[/TD]
[TD]3/26/19 10:00 AM[/TD]
[TD]TRUE[/TD]
[TD]Overlap 01[/TD]
[TD]"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."[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Employee A[/TD]
[TD]Customer 02[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 9:30 AM[/TD]
[TD]3/26/19 11:00 AM[/TD]
[TD]TRUE[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD]Overlap 01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]"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."[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Employee A[/TD]
[TD]Customer 02[/TD]
[TD]Task 2[/TD]
[TD]FALSE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 9:30 AM[/TD]
[TD]3/26/19 11:00 AM[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]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.
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Employee A[/TD]
[TD]Customer 04[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 11:00 AM[/TD]
[TD]3/26/19 12:00 PM[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]The "Has Overlap" field is FALSE because no other entry share this timeframe.[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Employee A[/TD]
[TD]Customer 05[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 12:00 PM[/TD]
[TD]3/26/19 1:00 PM[/TD]
[TD]TRUE[/TD]
[TD]Overlap 02[/TD]
[TD]"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."[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Employee B[/TD]
[TD]Customer 05[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 12:30 PM[/TD]
[TD]3/26/19 1:30 PM[/TD]
[TD]TRUE[/TD]
[TD]Overlap 02[/TD]
[TD]"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."[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Employee B[/TD]
[TD]Customer 07[/TD]
[TD]Task 1[/TD]
[TD]TRUE[/TD]
[TD]3/26/2019[/TD]
[TD]3/26/19 10:00 AM[/TD]
[TD]3/26/19 11:00 AM[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD]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.[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

For the first formula

<b>sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:114px;" /><col style="width:81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >End Date Time</td><td >Has Overlap</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">26/03/2019 10:00</td><td >TRUE</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=IF(SUMPRODUCT((E2="TRUE")*($E$2:$E$8="TRUE")*(($G$2:$G$8<=G2)*($H$2:$H$8>G2)+($G$2:$G$8<H2)*($H$2:$H$8>=H2)>0)*(($B$2:$B$8=B2)+($C$2:$C$8=C2)>0))>1,"TRUE","FALSE")</td></tr></table></td></tr></table> <br /><br />

 

[DMDGUSA]

Thank you for the reply. Unfortunately when implementing your function with the example provided all rows in columns I (Has Overlap field) return FALSE. As seen in the example, many should be TRUE with only a few FALSE options. Can you confirm and possibly guide me where I'm going wrong.

Thanks.

 

[DanteAmor]

This is the first formula with column corrections

Book1
	A	B	C	D	E	F	G	H	I
1	Employee	Customer	Task	Billable Task	Start Date	Start Date Time	End Date Time	Has Overlap	Overlap Group ID
2	Employee A	Customer 01	Task 1	TRUE	26/03/2019	26/03/2019 09:00	26/03/2019 10:00	TRUE
3	Employee A	Customer 02	Task 1	TRUE	26/03/2019	26/03/2019 09:30	26/03/2019 11:00	TRUE
4	Employee A	Customer 02	Task 2	FALSE	26/03/2019	26/03/2019 09:30	26/03/2019 11:00	FALSE
5	Employee A	Customer 04	Task 1	TRUE	26/03/2019	26/03/2019 11:00	26/03/2019 12:00	FALSE
6	Employee A	Customer 05	Task 1	TRUE	26/03/2019	26/03/2019 12:00	26/03/2019 13:00	TRUE
7	Employee B	Customer 05	Task 1	TRUE	26/03/2019	26/03/2019 12:30	26/03/2019 13:30	TRUE
8	Employee B	Customer 07	Task 1	TRUE	26/03/2019	26/03/2019 10:00	26/03/2019 11:00	FALSE
Hoja1

Cell Formulas
Range	Formula
H2	=IF(SUMPRODUCT((ROW(D2)<>ROW($D$2:$D$8))*(D2="TRUE")*($D$2:$D$8="TRUE")*(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8)*($G$2:$G$8>(G2-0.0006))>0)*(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,"TRUE","FALSE")

 

[DMDGUSA]

Thanks for working on this one with me Dante.

Your attached table did clarify the columns. But I'm still getting the same FALSE for each entry.

=IF(SUMPRODUCT((ROW(D2)<>ROW($D$2:$D$8))*(D2="TRUE")*($D$2:$D$8="TRUE")*(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8(G2-0.0006))>0)*(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,"TRUE","FALSE")

Maybe you can help me understand what I'm doing wrong?

I understand that your functions defines the following criteria:

1. The rows being compared must have different Billable Task value.
2. The rows must all have a Billable Task value of TRUE. (This seems to contradict the bullet 1 above. Maybe I'm misunderstanding this portion of the function.)
3. Time for all other rows must meet the following criteria:
   1. Start time for the relative row is after all available start times AND start time for the relative row begins before all other end times in the range.
   2. End time for the relative row is after all available start times AND end time for the relative row is before the end time of all other end times in the range.
   3. The Employee for the relative row is the same as all other employees in the range which meet the criteria.
   4. The Customer for the relative row is the same as all other Customers in the range which meet the criteria.

In your function, if the above criteria are met and the total count is greater than 1 (more than just the relative records being use for the criteria), then a value of TRUE is provided.

Do I understand the function correctly? Do you know why I am getting FALSE on all rows? Is it the logic between bullets 1 and 2 above.</g2)*($g$2:$g$8>

 

[DMDGUSA]

Man, it would really help to be able to attach the sheet I'm working on.

 

[DanteAmor]

What do you have in the cell?
"TRUE" or TRUE

The formula considers that you have the text "TRUE", but if you have the value TRUE, then change the formula to the following:

=IF(SUMPRODUCT((ROW(D2)<>ROW($D$2:$D$8))*(D2=TRUE)*($D$2:$D$8=TRUE)*(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8<G2)*($G$2:$G$8>(G2-0.0006))>0)*(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,TRUE,FALSE)

 

[DMDGUSA]

ha! How did I miss that? Thank you. Now it's working on my sheet too.

I'd love to understand this function combination a little more so I can add criteria. For example, if I was going to add a department, would I add it to the last set of criteria. I.e. "...(($A$2:$A$8=A2)+($B$2:$B$8=B2)+(NewCriteriaRange=NewCriteria)>0))>0..."? Am I correctly understanding the function?

Thanks again for all of the help.

 

[DanteAmor]

Now, the explanation goes:
Complete formula

=IF(SUMPRODUCT(
(ROW(D2)<>ROW($D$2:$D$8))*
(D2="TRUE")*
($D$2:$D$8="TRUE")*
(($F$2:$F$8<(F2+0.0006))*($G$2:$G$8>F2)+($F$2:$F$8<G2)*($G$2:$G$8>(G2-0.0006))>0)*
(($A$2:$A$8=A2)+($B$2:$B$8=B2)>0))>0,"TRUE","FALSE")



= IF (SUMPRODUCT (
(ROW(D2) <> ROW ($D$2:$D$8))
Only check the rows different to D2, that is, it does not compare itself, since the result would be true.


* (D2=TRUE) *
Only check if "Billable Task" is TRUE, as in the case of row 4, it is FALSE, then it does not perform the comparison.


($D$2:$D$8=TRUE) *
Only compare with the other rows if "Billable Task" equals TRUE


(($F$2:$F$8 <(F2 + 0.0006)) * ($ G $ 2: $ G $ 8> F2)
If the range of startDate is less than my startDate and the range of endDate is greater than my startDate.
F2 + 0.00006 means 9:00 + one minute = 9:01, without this, if you compare a range of 8 to 9 against 9 to 10 it would be true, that's why I increase it by one minute


+ (this is important, it is to ask if the previous match is met or if the following condition is met)


($F$2:$F$8<g2) *="" ($g$2:$g$8=""> (G2-0.0006))> 0) *
Compare the final date with the date ranges


(
($A$2:$A$8 = A2)
If the employee matches
+
Or if the customer matches
($B$2:$B$8 = B2)> 0)


)> 0, TRUE, FALSE)
If all the conditions are met it will result in> 0 then TRUE, if any condition is not met, then the result is 0 (FALSE)

---

</g2)><g2) *="" ($g$2:$g$8="">I have problems to solve formula 2


</g2)>

 

[DMDGUSA]

Your explanation is very clear and the solution is eloquent. Thank you so much for taking the time to share. I feel like I can now use this with flexibility for other situations.

I can appreciate that the second portion, the Overlap Group ID is a tough one. Maybe someone else will have some clever ideas.