Date Range Conflict Checker

[Marmasek]

I am trying to set up a spreadsheet that detects conflicts within a date range amongst the same names. For example:

Name	Start	End	Conflict?
Blue	10-Feb-20	16-Feb-20	Yes
Blue	9-Feb-20	15-Feb-20	Yes
Red	1-Feb-20	5-Feb-20	No
Red	10-Feb-20	16-Feb-20	Yes
Red	12-Feb-20	15-Feb-20	Yes

You can see that within the name Blue, there is a conflict during the dates 10-15 Feb inclusively. Similarly, within the name Red, there is a conflict during the dates 12-15 Feb inclusively.

In order to detect the conflict, I have entered the following array formula into D2:
{=CHOOSE(1+MAX((A2=A$2:A$6)*(B2>=B$2:B$6)*(B2<=C$2:C$6)*(ROW()<>ROW(A$2:A$6)),(A2=A$2:A$6)*(C2>=B$2:B$6)*(C2<=C$2:C$6)*(ROW()<>ROW(A$2:A$6))),"No","Yes")}

It detects conflicts when a date is within another conflicted date but not when the dates overlap. For example (the same table as above, but with the array formula put into column D):

Name	Start	End	Conflict?
Blue	10-Feb-20	16-Feb-20	Yes
Blue	9-Feb-20	15-Feb-20	Yes
Red	1-Feb-20	5-Feb-20	No
Red	10-Feb-20	16-Feb-20	No
Red	12-Feb-20	15-Feb-20	Yes

As you can see, there should be an indicated conflict on D5, but it shows as No.

If someone could help me find a formula that works to detect all date range conflicts as I demonstrated in the first table above, I would appreciate it very much.

Thank you.

 

[lrobbo314]

Book1
	B	C	D	E
5	Name	Start	End	Conflict
6	Blue	2/10/2020	2/16/2020	Yes
7	Blue	2/9/2020	2/15/2020	Yes
8	Red	2/1/2020	2/5/2020	No
9	Red	2/10/2020	2/16/2020	Yes
10	Red	2/12/2020	2/15/2020	Yes
Sheet12

Cell Formulas
Range	Formula
E6:E10	E6	=CHOOSE((SUMPRODUCT((C6<=$D$6:$D$10)*(D6>=$C$6:$C$10))>1)+1,"No","Yes")

 

[Marmasek]

Thank you for your reply.

This formula detects conflicts as I would like them to, however it doesn't take the name into account. Using the formula you gave me, altering some dates and names, I have this result:

Blue	10-Feb-20​	16-Feb-20​	Yes
Blue	09-Feb-20​	15-Feb-20​	Yes
Red	13-Mar-20​	14-Mar-20​	Yes
Blue	10-Mar-20​	16-Mar-20​	Yes
Red	12-Mar-20​	15-Mar-20​	Yes

However the result I am looking for is as follows:

Blue	10-Feb-20​	16-Feb-20​	Yes
Blue	09-Feb-20​	15-Feb-20​	Yes
Red	13-Mar-20​	14-Mar-20​	Yes
Blue	10-Mar-20​	16-Mar-20​	No
Red	12-Mar-20​	15-Mar-20​	Yes

The third blue item has no conflict since it doesn't conflict with the other blue items, even if the dates do conflict with the red items.