Create Unique Overlapping Date Ranges from List of Dates

Hi all

Looking for any help that is out there but not sure if this should fall under formula's or vba code help.

Using the below data, I need a formula or vba code to determine all the different overlapping date ranges that exist when looking at combining all 3 legs.

Market Travel Start Travel End
Leg 1 01/06/15 11/11/15
Leg 2 22/05/15 05/06/15
Leg 2 01/07/15 09/09/15
Leg 2 10/10/15 22/11/15
Leg 3 10/07/15 05/09/15
Leg 3 01/11/15 25/12/15


So when looking at a combination of "Leg 1,2,3". My results using the above data would be below. These are the only 2 date ranges that overlap for all 3 Legs.

Market Travel Start Travel End
Leg 1,2,3 10/07/15 05/09/15
Leg 1,2,3 01/11/15 11/11/15

I hope this makes sense.

Thanks


Dean

Hi Richard

Yes Evolta is correct.

I only want the results to pull travel periods which are available for all three legs.

So with table of dates, the only travel periods where all three are overlapping are "10/07/15 to 05/09/15" and "01/11/15 to 11/11/15".

If we look at the first date range of "10/07/15 to 05/09/15" and de-construct this, the logic is as follows:

- To get the 1st date of 10/07/15 - Leg 1 has a date starting from "01/06/15 to 11/11/15" and Leg 2 has a date starting from "01/07/15 to 09/09/15" however as Leg 3 has a date starting from "10/07/15 to 05/09/15". So the earliest possible date that all 3 overlap is 10/07/15.
- For the end date of 05/09/15 - looking at the dates above, as Leg 3 ends on "05/09/15", this is the last time from the above dates that all 3 legs are available.


I hope that helps.

Thanks