Date Range Conflict checker

[capslo]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name
[/TD]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Conflict?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]12/1/14[/TD]
[TD]12/2/14[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]12/1/14[/TD]
[TD]12/2/14[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apples[/TD]
[TD]11/30/14[/TD]
[TD]12/2/14[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apples[/TD]
[TD]12/4/14[/TD]
[TD]12/5/14[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Apples[/TD]
[TD]11/30/14[/TD]
[TD]12/1/14[/TD]
[TD]YES[/TD]
[/TR]
</tbody>[/TABLE]

Hoping for some help. I'm open to any solution.

I want to compare the "Name" value in column A and if it matches elsewhere in A, then check to see if there is overlap of Start and End Dates with anywhere else the Name matches.

Hopefully the above table helps explain it.

Seems complicated for Excel, but want to know how this can be done.

 

[ztodd]

It's a little complicated- but can be done with an array formula. In cell D2- press F2, paste this into it, then press Control-Shift-Enter :

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

 

[ztodd]

Forgot to say,
Then just fill it down.

To partly explain- this is an array formula, which means it does a loop through the ranges, performing a certain formula on each row in the range- so taking only cell F2 for example, it will loop from rows 2 through 6 (because those are the rows that the different ranges referenced in there range between), and will evaluate the part of the formula which is inside the MAX function for that row. The results are all passed back to the max function, then the rest of the formula continues normally. Like this-

Loop from rows 2 to 6:
(A2=A$2)*(B2>=B$2)*(B2<=C$2)*(ROW()<>ROW(A$2)),(A2=A$2)*(C2>=B$2)*(C2<=C$2)*(ROW()<>ROW(A$2)) Result : 0 (see below for explanation)
(A2=A$3)*(B2>=B$3)*(B2<=C$3)*(ROW()<>ROW(A$3)),(A2=A$3)*(C2>=B$3)*(C2<=C$3)*(ROW()<>ROW(A$3)) Result : 0
(A2=A$4)*(B2>=B$4)*(B2<=C$4)*(ROW()<>ROW(A$4)),(A2=A$4)*(C2>=B$4)*(C2<=C$4)*(ROW()<>ROW(A$4)) Result : 1
(A2=A$5)*(B2>=B$5)*(B2<=C$5)*(ROW()<>ROW(A$5)),(A2=A$5)*(C2>=B$5)*(C2<=C$5)*(ROW()<>ROW(A$5)) Result : 0
(A2=A$6)*(B2>=B$6)*(B2<=C$6)*(ROW()<>ROW(A$6)),(A2=A$6)*(C2>=B$6)*(C2<=C$6)*(ROW()<>ROW(A$6)) Result : 1

These values are returned back to the MAX function-- MAX(0, 0, 1, 0, 1) which returns 1 of course. Then to finish up, CHOOSE(1+1,"No","Yes") returns Yes.

Explanation of loop results:

Each evaluation test within the formula, like (A2=A$2) will simply return a zero if false or a one if true. So this is a way of doing a logical AND for the specified criteria. These are the criteria we are 'AND'ing together :

First parameter of the MAX :
- the name matches (A2=A$2)
- start date is between the start and end dates of the row being compared to (B2>=B$2)*(B2<=C$2)
- we are not comparing the row to itself (ROW()<>ROW(A$2))

Second parameter of the MAX :
- the name matches (A2=A$2)
- end date is between the start and end dates of the row being compared to (C2>=B$2)*(C2<=C$2)
- we are not comparing the row to itself (ROW()<>ROW(A$2))

Having the two parameters to the MAX function is like doing a logical OR between them, since the results are 1=true, 0=false.

If you really want to know how this all works, read through this a few times.

 

[capslo]

Thank you for such a great explanation. It's been a long time since I've been deep in Excel. I figured the answer was probably in an array formula but my skills are too rusty. I've never used the choose function either. Once again many thanks!

 

[Marmasek]

This is a great formula. I have been using it to track vacation days amongst employees in various departments, where I use the department name as the “name” and the vacation day range. It has allowed me to ensure people in the same department don’t take their vacations at the same time.

My question now is whether it is possible to expand the formula so that it can check multiple columns of date ranges. For example the columns would be:

A(deptname) B(daterange1) C(daterange2) D(daterange3) E(conflict?)

Basically I would like to see if someone in the same department would have a conflict in their vacation day with another employee, but checking all the daterange columns since some will be truncating their vacation days.