Highlight matching cells in at least 2 arrays

Hi there! I'm new to using more advanced formulas, and I've not yet hit on the right combination of factors to solve my problem.

I've got 3 sets of dates, and I need to make sure that there are no overlaps or conflicts between the data sets. If the schedule for one product starts on Dec 1 and ends Dec 8, I need to make sure none of the other schedules start/end on either Dec 1 or 8. The list of dates for each product is extensive, which is why I'm looking for a formula instead of casting an eye over it manually.

For example:

Data Set #1

Thursday, November 03, 16 - Saturday, November 12, 16
Saturday, November 12, 16 - Tuesday, November 22, 16
Tuesday, November 22, 16 - Saturday, December 10, 16

needs to not conflict with

Data Set #2

Sunday, November 06, 16 - Tuesday, November 15, 16
Tuesday, November 15, 16 - Thursday, November 24, 16
Thursday, November 24, 16 - Friday, December 02, 16

or with

Data Set #3

Tuesday, November 08, 16 - Thursday, November 17, 16
Thursday, November 17, 16 - Monday, December 05, 16
Monday, December 05, 16 - Wednesday, December 14, 16

I need a formula to check these against each other. If it needs to be in separate functions (ie. "Does #1 conflict with #2?" and "Does #1 conflict with #3?"), that is okay. I'd also prefer that any dates that do pose a conflict are highlighted for easy notice.

Does anyone know how to do what I'm looking for? Using Excel 2013, Windows 7.

Thanks!

Definitely. I just wanted to make it clear that these are closed date ranges. They do not appear as text.

Originally Posted by Tony Valko

Each single date is in its own cell, right?

For example:

A2: Thursday, November 03, 16
B2: Saturday, November 12, 16

And even more important, these are real Excel dates and not TEXT strings?

Thanks for taking an interest, Tony.

Attached is a simplified version of what I'm working with. The Start and End dates are less important; it's the Embark and Disembark days that need to avoid conflict. Manually highlighted in red are dates that I already know to be in conflict. Below each table is my attempt to use COUNTIFS to solve the issue, but it clearly hasn't identified the known problem, and is therefore not going to work.

I could have removed the "Nights" column for the example, but I have a feeling it's going to throw off the ability to use arrays, so I left it in to show the reality of what I'm working with.

Even more advanced, but not exactly necessary, is the possibility of involving the EMB and DIS columns on the far right. The dates can conflict, if for example, one product is Embarking from USH and the other is Disembarking in PUQ. However, we will still be miles ahead of where we are currently if we can at least identify matching dates.

It's useful to know that the dates are already using formulas to calculate themselves, based on manual entry of the number of nights of each unit and the starting date. If it's possible to enter a formula into conditional formatting to highlight any cell that matches the results of another cell, that'd be my goal solution.

I hope that's enough to work with, but let me know if there are any details I've left out.

Tony!! I don't think I was very clearheaded when I attempted to implement your solution before; I didn't think it worked for my needs. I was wrong - it works perfectly! I was even able to get it to work for another outside component that affects one of the data sets, and put those conflicts in another format.

Thank you very much. This was wonderfully solved.