Matching Multiple date ranges with other date ranges

[jabaker54]

I have a data set where I am trying to get excel to recognize when two data sets overlap one another. In both tables I have an Activity, a name, end date and start date. If two date ranges from the two tables overlap, and the names of the two people match, I want that row to highlight. The tables are below

John
Bill
Paul
Tim
Paul
Frank
Tim
John
Christy
Sally
Bill

<tbody>
[TD="class: xl64"]Activity[/TD]
[TD="class: xl64, width: 64"]Name[/TD]
[TD="class: xl64, width: 84"]Start [/TD]
[TD="class: xl64, width: 84"]End[/TD]

[TD="class: xl64"]1[/TD]

[TD="class: xl67, align: right"]12-Oct-2014[/TD]
[TD="class: xl67, align: right"] 18-Oct-2014[/TD]

[TD="class: xl64"]2[/TD]

[TD="class: xl67, align: right"]12-Nov-2014[/TD]
[TD="class: xl67, align: right"]18-Nov-2014[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl66"]Sally[/TD]
[TD="class: xl68, align: right"]12-Dec-2014[/TD]
[TD="class: xl68, align: right"]18-Dec-2014[/TD]

[TD="class: xl64"]4[/TD]

[TD="class: xl67, align: right"]12-Jan-2015[/TD]
[TD="class: xl67, align: right"]18-Jan-2015[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl66"]Christy[/TD]
[TD="class: xl68, align: right"]12-Feb-2015[/TD]
[TD="class: xl68, align: right"]18-Feb-2015[/TD]

[TD="class: xl64"]6[/TD]

[TD="class: xl67, align: right"]12-Mar-2015[/TD]
[TD="class: xl67, align: right"]18-Mar-2015[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl66"]Frank[/TD]
[TD="class: xl68, align: right"]12-Apr-2015[/TD]
[TD="class: xl68, align: right"]18-Apr-2015[/TD]

[TD="class: xl64"]Activity[/TD]
[TD="class: xl64"]Name[/TD]
[TD="class: xl64"]Start[/TD]
[TD="class: xl64"]End[/TD]

[TD="class: xl64"]1[/TD]

[TD="class: xl67, align: right"]30-Jan-2015[/TD]
[TD="class: xl67, align: right"]31-Jan-2015[/TD]

[TD="class: xl64"]2[/TD]

[TD="class: xl67, align: right"]10-Apr-2015[/TD]
[TD="class: xl67, align: right"]13-Apr-2015[/TD]

[TD="class: xl64"]3[/TD]

[TD="class: xl67, align: right"]20-Mar-2015[/TD]
[TD="class: xl67, align: right"]21-Mar-2015[/TD]

[TD="class: xl64"]4[/TD]

[TD="class: xl67, align: right"]8-Oct-2014[/TD]
[TD="class: xl67, align: right"]10-Oct-2014[/TD]

[TD="class: xl64"]5[/TD]

[TD="class: xl67, align: right"]12-Feb-2015[/TD]
[TD="class: xl67, align: right"]12-Feb-2015[/TD]

[TD="class: xl64"]6[/TD]

[TD="class: xl67, align: right"]11-Dec-2014[/TD]
[TD="class: xl67, align: right"]12-Dec-2014[/TD]

[TD="class: xl64"]7[/TD]

[TD="class: xl67, align: right"]20-Nov-2014[/TD]
[TD="class: xl67, align: right"]25-Nov-2014[/TD]

</tbody>

 

[steve the fish]

Pasting you data into excel as seen here your second table is in A11:D18. So click A12, press conditional formatting, new rule, use formula to determine.., in the box place this:

=VLOOKUP($B12,$B$2:$D$8,3,0)>$C12

Format to however required then press OK.

In the resultant applies to box place this:

=$A$12:$D$18

Press OK.

 

[jabaker54]

=VLOOKUP($B12,$B$2:$D$8,3,0)>$C12

It worked! How does this vlookup work? What is the "3,0"?

 

[steve the fish]

It looks for the value of B12 in B2:B8 and returns the value in the same row but the 3rd column to the right INCLUDING the original column. So 3rd is D (B,C,D). The 0 means it needs to find an exact match of B12 in B2:B8. This lookup is looking for the end date in first table then seeing if it is greater (later) than the start date in 2nd table.