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

Activity	Name	Start	End
1	John	12-Oct-2014	18-Oct-2014
2	Bill	12-Nov-2014	18-Nov-2014
3	Sally	12-Dec-2014	18-Dec-2014
4	Paul	12-Jan-2015	18-Jan-2015
5	Christy	12-Feb-2015	18-Feb-2015
6	Tim	12-Mar-2015	18-Mar-2015
7	Frank	12-Apr-2015	18-Apr-2015
Activity	Name	Start	End
1	Paul	30-Jan-2015	31-Jan-2015
2	Frank	10-Apr-2015	13-Apr-2015
3	Tim	20-Mar-2015	21-Mar-2015
4	John	8-Oct-2014	10-Oct-2014
5	Christy	12-Feb-2015	12-Feb-2015
6	Sally	11-Dec-2014	12-Dec-2014
7	Bill	20-Nov-2014	25-Nov-2014

<tbody>
</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.