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]

I'm glad to help you. I appreciate your kind comments.

 

[nmk7]

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)>

Sorry I know this is an old thread, and you might not see this, but I am having difficulties setting up a formula using your example. I am needing to see if there is an overlap in services that have the status of Kept, but right now using your formula it is returning TRUE for every service that is Kept regardless if there is an overlap or not. I am also not quite sure how to have the Service date included so it will ignore overlapping times from different days.
The formula used without accounting for date at the moment: =IF(SUMPRODUCT((ROW(F2)<>ROW($F$2:$F$3000))*(F2="Kept")*($F$2:$F$3000="Kept")*(($C$2:$C$3000<(C2+0.0006))*($D$2:$D$3000>C2)+($C$2:$C$3000)*($D$2:$D$3000>(D2-0.0006))>0)*(($A$2:$A$3000=A2)>0))>0,"TRUE","FALSE")

Client

Service Date

Begin Time

End Time

Minutes

Status

Type

Activity

Procedure

Units

Staff

Program

Org Code

Billing Status

Has Overlap

Test, Client (9999) 12/31/2000

06/04/2023

12:00 AM

12:01 AM

1

Kept

Client

PT1

1

Test, Staff

PT1

PTS

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

09:00 AM

10:00 AM

60

Error

Group

PTGS

0

Test, Staff

PT1

PTS

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

01:00 PM

02:00 PM

60

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

1:30 PM

04:00 PM

120

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

03:00 PM

04:00 PM

60

Kept

Client

PTIF

0

Test, Staff

PT1

PTS

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

07:30 PM

08:00 PM

30

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

TRUE

 

[DanteAmor]

Hi @nmk7
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I am also not quite sure how to have the Service date included so it will ignore overlapping times from different days.

I suggest you create two columns with the sum of the date and time, see the following example:

varios 12jun2023.xlsm
	A	B	C	D	E	F	G	H									Q
1	Client	Service Date	Begin Time	End Time	Begin Time	End Time	Minutes	Status									Has Overlap
2	Test, Client (9999) 12/31/2000	06/04/2023	12:00:00 a. m.	12:01:00 a. m.	06/04/2023 00:00	06/04/2023 00:01	1	Kept									FALSE
3	Test, Client (9999) 12/31/2000	06/04/2023	09:00:00 a. m.	10:00:00 a. m.	06/04/2023 09:00	06/04/2023 10:00	60	Error									FALSE
4	Test, Client (9999) 12/31/2000	06/04/2023	01:00:00 p. m.	02:00:00 p. m.	06/04/2023 13:00	06/04/2023 14:00	60	Kept									TRUE
5	Test, Client (9999) 12/31/2000	06/04/2023	01:30:00 p. m.	04:00:00 p. m.	06/04/2023 13:30	06/04/2023 16:00	120	Kept									TRUE
6	Test, Client (9999) 12/31/2000	06/04/2023	03:00:00 p. m.	04:00:00 p. m.	06/04/2023 15:00	06/04/2023 16:00	60	Kept									TRUE
7	Test, Client (9999) 12/31/2000	06/04/2023	07:30:00 p. m.	08:00:00 p. m.	06/04/2023 19:30	06/04/2023 20:00	30	Kept									FALSE
Hoja1

Cell Formulas
Range	Formula
E2:F7	E2	=$B2+LEFT(C2,8)
Q2:Q7	Q2	=IF(SUMPRODUCT((ROW(F2)<>ROW($F$2:$F$7))*($H$2:$H$7="Kept")* (($E$2:$E$7<(E2+0.0006))*($F$2:$F$7>E2)+($E$2:$E$7<F2)*($F$2:$F$7>(F2-0.0006))>0)* (($A$2:$A$7=A2)>0))>0,"TRUE","FALSE")

--------------
If it's not what you need, I suggest creating a new thread.
Cordially
Dante Amor
--------------​

 

[nmk7]

Hi @nmk7
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.




I suggest you create two columns with the sum of the date and time, see the following example:

varios 12jun2023.xlsm
	A	B	C	D	E	F	G	H									Q
1	Client	Service Date	Begin Time	End Time	Begin Time	End Time	Minutes	Status									Has Overlap
2	Test, Client (9999) 12/31/2000	06/04/2023	12:00:00 a. m.	12:01:00 a. m.	06/04/2023 00:00	06/04/2023 00:01	1	Kept									FALSE
3	Test, Client (9999) 12/31/2000	06/04/2023	09:00:00 a. m.	10:00:00 a. m.	06/04/2023 09:00	06/04/2023 10:00	60	Error									FALSE
4	Test, Client (9999) 12/31/2000	06/04/2023	01:00:00 p. m.	02:00:00 p. m.	06/04/2023 13:00	06/04/2023 14:00	60	Kept									TRUE
5	Test, Client (9999) 12/31/2000	06/04/2023	01:30:00 p. m.	04:00:00 p. m.	06/04/2023 13:30	06/04/2023 16:00	120	Kept									TRUE
6	Test, Client (9999) 12/31/2000	06/04/2023	03:00:00 p. m.	04:00:00 p. m.	06/04/2023 15:00	06/04/2023 16:00	60	Kept									TRUE
7	Test, Client (9999) 12/31/2000	06/04/2023	07:30:00 p. m.	08:00:00 p. m.	06/04/2023 19:30	06/04/2023 20:00	30	Kept									FALSE
Hoja1

Cell Formulas
Range	Formula
E2:F7	E2	=$B2+LEFT(C2,8)
Q2:Q7	Q2	=IF(SUMPRODUCT((ROW(F2)<>ROW($F$2:$F$7))*($H$2:$H$7="Kept")* (($E$2:$E$7<(E2+0.0006))*($F$2:$F$7>E2)+($E$2:$E$7<F2)*($F$2:$F$7>(F2-0.0006))>0)* (($A$2:$A$7=A2)>0))>0,"TRUE","FALSE")

--------------
If it's not what you need, I suggest creating a new thread.
Cordially
Dante Amor
--------------​

It's working better, but it is still treating overlaps of time with the Status of Kept and Error as a TRUE when all instances of ERROR should come back as FALSE (See bottom row of table below). Our system outputs reports as excel spreadsheets, so I'm building a template that I can just copy and paste the info into to check for overlaps over the last week of services for a particular set of clients, that's why there's all the extra columns of info that is useless for the task at hand.

Formula used: =IF(SUMPRODUCT((ROW(P2)<>ROW($P$2:$P$3000))*($F$2:$F$3000="Kept")*(($O$2:$O$3000<(O2+0.0006))*($P$2:$P$3000>O2)+($O$2:$O$3000<P2)*($P$2:$P$3000>(P2-0.0006))>0)*(($A$2:$A$3000=A2)>0))>0,"TRUE","FALSE")

Client

Service Date

Begin Time

End Time

Minutes

Status

Type

Activity

Procedure

Units

Staff

Program

Org Code

Billing Status

Date + Begin Time

Date + End Time

Has Overlap

Test, Client (9999) 12/31/2000

06/04/2023

12:00 AM

12:01 AM

1

Kept

Client

PT1

1

Test, Staff

PT1

PTS

6/4/23 0:00

6/4/23 0:01

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

09:00 AM

10:00 AM

60

Error

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 9:00

6/4/23 10:00

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

01:00 PM

02:00 PM

60

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 13:00

6/4/23 14:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

01:00 PM

04:00 PM

120

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 13:00

6/4/23 16:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

03:00 PM

04:00 PM

60

Kept

Client

PTIF

0

Test, Staff

PT1

PTS

6/4/23 15:00

6/4/23 16:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

07:30 PM

08:00 PM

30

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 19:30

6/4/23 20:00

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

08:00 PM

08:30 PM

30

Error

Group

MRUN

0

Test, Staff

PT1

BH

6/4/23 20:00

6/4/23 20:30

FALSE

Test, Client (9999) 12/31/2000

06/05/2023

12:00 AM

12:01 AM

1

Kept

Client

PT1

1

Test, Staff

PT1

PTS

6/5/23 0:00

6/5/23 0:01

FALSE

Test, Client (9999) 12/31/2000

06/05/2023

09:00 AM

10:00 AM

60

Error

Group

PTGS

0

Test, Staff

PT1

PTS

6/5/23 9:00

6/5/23 10:00

TRUE

 

[nmk7]

Hi @nmk7
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.




I suggest you create two columns with the sum of the date and time, see the following example:

varios 12jun2023.xlsm
	A	B	C	D	E	F	G	H									Q
1	Client	Service Date	Begin Time	End Time	Begin Time	End Time	Minutes	Status									Has Overlap
2	Test, Client (9999) 12/31/2000	06/04/2023	12:00:00 a. m.	12:01:00 a. m.	06/04/2023 00:00	06/04/2023 00:01	1	Kept									FALSE
3	Test, Client (9999) 12/31/2000	06/04/2023	09:00:00 a. m.	10:00:00 a. m.	06/04/2023 09:00	06/04/2023 10:00	60	Error									FALSE
4	Test, Client (9999) 12/31/2000	06/04/2023	01:00:00 p. m.	02:00:00 p. m.	06/04/2023 13:00	06/04/2023 14:00	60	Kept									TRUE
5	Test, Client (9999) 12/31/2000	06/04/2023	01:30:00 p. m.	04:00:00 p. m.	06/04/2023 13:30	06/04/2023 16:00	120	Kept									TRUE
6	Test, Client (9999) 12/31/2000	06/04/2023	03:00:00 p. m.	04:00:00 p. m.	06/04/2023 15:00	06/04/2023 16:00	60	Kept									TRUE
7	Test, Client (9999) 12/31/2000	06/04/2023	07:30:00 p. m.	08:00:00 p. m.	06/04/2023 19:30	06/04/2023 20:00	30	Kept									FALSE
Hoja1

Cell Formulas
Range	Formula
E2:F7	E2	=$B2+LEFT(C2,8)
Q2:Q7	Q2	=IF(SUMPRODUCT((ROW(F2)<>ROW($F$2:$F$7))*($H$2:$H$7="Kept")* (($E$2:$E$7<(E2+0.0006))*($F$2:$F$7>E2)+($E$2:$E$7<F2)*($F$2:$F$7>(F2-0.0006))>0)* (($A$2:$A$7=A2)>0))>0,"TRUE","FALSE")

--------------
If it's not what you need, I suggest creating a new thread.
Cordially
Dante Amor
--------------​

I just realized I didn't include the row that would be needed to troubleshoot the problem. It's the last two rows:

Client

Service Date

Begin Time

End Time

Minutes

Status

Type

Activity

Procedure

Units

Staff

Program

Org Code

Billing Status

Date + Begin Time

Date + End Time

Has Overlap

Test, Client (9999) 12/31/2000

06/04/2023

12:00 AM

12:01 AM

1

Kept

Client

PT1

1

Test, Staff

PT1

PTS

6/4/23 0:00

6/4/23 0:01

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

09:00 AM

10:00 AM

60

Error

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 9:00

6/4/23 10:00

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

01:00 PM

02:00 PM

60

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 13:00

6/4/23 14:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

01:00 PM

04:00 PM

120

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 13:00

6/4/23 16:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

03:00 PM

04:00 PM

60

Kept

Client

PTIF

0

Test, Staff

PT1

PTS

6/4/23 15:00

6/4/23 16:00

TRUE

Test, Client (9999) 12/31/2000

06/04/2023

07:30 PM

08:00 PM

30

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/4/23 19:30

6/4/23 20:00

FALSE

Test, Client (9999) 12/31/2000

06/04/2023

08:00 PM

08:30 PM

30

Error

Group

MRUN

0

Test, Staff

PT1

BH

6/4/23 20:00

6/4/23 20:30

FALSE

Test, Client (9999) 12/31/2000

06/05/2023

12:00 AM

12:01 AM

1

Kept

Client

PT1

1

Test, Staff

PT1

PTS

6/5/23 0:00

6/5/23 0:01

FALSE

Test, Client (9999) 12/31/2000

06/05/2023

09:00 AM

10:00 AM

60

Error

Group

PTGS

0

Test, Staff

PT1

PTS

6/5/23 9:00

6/5/23 10:00

TRUE

Test, Client (9999) 12/31/2000

06/05/2023

09:00 AM

10:00 AM

60

Kept

Group

PTGS

0

Test, Staff

PT1

PTS

6/5/23 9:00

6/5/23 10:00

FALSE

 

[nmk7]

Hi @nmk7
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.




I suggest you create two columns with the sum of the date and time, see the following example:

varios 12jun2023.xlsm
	A	B	C	D	E	F	G	H									Q
1	Client	Service Date	Begin Time	End Time	Begin Time	End Time	Minutes	Status									Has Overlap
2	Test, Client (9999) 12/31/2000	06/04/2023	12:00:00 a. m.	12:01:00 a. m.	06/04/2023 00:00	06/04/2023 00:01	1	Kept									FALSE
3	Test, Client (9999) 12/31/2000	06/04/2023	09:00:00 a. m.	10:00:00 a. m.	06/04/2023 09:00	06/04/2023 10:00	60	Error									FALSE
4	Test, Client (9999) 12/31/2000	06/04/2023	01:00:00 p. m.	02:00:00 p. m.	06/04/2023 13:00	06/04/2023 14:00	60	Kept									TRUE
5	Test, Client (9999) 12/31/2000	06/04/2023	01:30:00 p. m.	04:00:00 p. m.	06/04/2023 13:30	06/04/2023 16:00	120	Kept									TRUE
6	Test, Client (9999) 12/31/2000	06/04/2023	03:00:00 p. m.	04:00:00 p. m.	06/04/2023 15:00	06/04/2023 16:00	60	Kept									TRUE
7	Test, Client (9999) 12/31/2000	06/04/2023	07:30:00 p. m.	08:00:00 p. m.	06/04/2023 19:30	06/04/2023 20:00	30	Kept									FALSE
Hoja1

Cell Formulas
Range	Formula
E2:F7	E2	=$B2+LEFT(C2,8)
Q2:Q7	Q2	=IF(SUMPRODUCT((ROW(F2)<>ROW($F$2:$F$7))*($H$2:$H$7="Kept")* (($E$2:$E$7<(E2+0.0006))*($F$2:$F$7>E2)+($E$2:$E$7<F2)*($F$2:$F$7>(F2-0.0006))>0)* (($A$2:$A$7=A2)>0))>0,"TRUE","FALSE")

--------------
If it's not what you need, I suggest creating a new thread.
Cordially
Dante Amor
--------------​

Hello,

Sorry for all the messages about this, but I can't quite figure this out. I'm working on this project some more this morning, and the formula is treating all overlaps of clients as a True event. I'm not needing to see if client #1 and client #2 have overlapping services. Just checking to see if each individual client is being billed by multiple staff at one time. So the outcome of the table below should be True, False, True, False. So in this scenario Staff member Jane ran a group session with clients #1-3, and John supposedly ran an individual session with client #1.

Formula used: =IF(SUMPRODUCT((ROW(P5)<>ROW($P$2:$P$2999))*(F5="Kept")*($F$2:$F$2999="Kept")*(($O$2:$O$2999<(O5+0.0006))*($P$2:$P$2999>O5)+($O$2:$O$2999<P5)*($P$2:$P$2999>(P5-0.0006))>0)*(($A$2:$A$2999=A5)>0))>0,"True","FALSE")

Any guidance you can give?

Client

Service Date

Begin Time

End Time

Minutes

Status

Type

Activity

Procedure

Units

Staff

Program

Org Code

Billing Status

Date + Begin Time

Date + End Time

Has Overlap

Test, Client (0001) 1/1/2001

05/26/2023

08:00 AM

09:00 AM

60

Kept

Group

PTG

Test, Jane

PT

PTR

5/26/23 8:00

5/26/23 9:00

True

Test, Client (0002) 2/2/2002

05/26/2023

08:00 AM

09:00 AM

60

Kept

Group

PTG

Test, Jane

PT

PTR

5/26/23 8:00

5/26/23 9:00

True

Test, Client (0001) 1/1/2001

05/26/2023

08:00 AM

09:00 AM

60

Kept

Group

PTI

Test, John

PT

PTR

5/26/23 8:00

5/26/23 9:00

True

Test, Client (0003) 3/3/2003

05/26/2023

08:00 AM

09:00 AM

61

Kept

Group

PTG

Test, Jane

PT

PTR

5/26/23 8:00

5/26/23 9:00

True