Find common dates in Excel spreadsheet

[BrianLe]

In Excel 2003, I have a spreadsheet that has numerous start and end dates for events for 3 sites. See example below.

Site 1 Site 2 Site 3

Start End Start End Start End
1/1/2011 1/5/2011 1/9/2001 1/10/2011 1/13/2011 1/16/2011
1/11/2011 1/15/2011 1/14/2011 1/17/2011 1/18/2011 1/21/2011

For each site, the events are in chronological order.

I would like to know when there were common dates for events at all 3 sites. In the example above, it would be 1/14 - 1/15. The first event for Site 3 has dates in common with the second event for Site 1 and Site 2.

I started trying to use if statements (=if C3>A3,1,0) to compare the dates, but that seemed to get messy since the common events could be on different rows in the spreadsheet.

Thanks,

Brian

 

[xlhelp]

Rather than re-invent the wheel, please look here

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[mintjulep]

Normalize your data such that each line contains unique information about one and only one event.

Site Start End
1 1/1/2011 1/5/2011
2 1/6/2011 1/8/2011
2 1/10/2011 1/15/2011
1 2/2/2011 2/5/2011
3 1/19/2011 1/25/2011

etc.

Then your answer is simply a pivot table away.

 

[SkipVought]

faq68-5287

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BrianLe]

I tried xlhelp's suggestion by using "Number Of Days Common To Two Intervals", but I can't figure out how to use it to check for time intervals when the event dates to be checked could be on different rows. I normalized the table and tried a pivot table (I don't have a whole lot of experience with pivot tables) but can't figure out how to compare the event dates for commonality.

Any more suggestions are welcomed.

Thanks,

Brian

 

[SkipVought]

[tt]
Start End Site Event
1/1/2011 1/5/2011 1 1
1/11/2011 1/15/2011 1 2
1/9/2011 1/10/2011 2 1
1/14/2011 1/17/2011 2 2
1/13/2011 1/16/2011 3 1
1/18/2011 1/21/2011 3 2
[/tt]
Formula on each row using Named Ranges
[tt]
=SUMPRODUCT((B2>=Start)*(A2<=End))>1
[/tt]
Result
[tt]
FALSE
TRUE
FALSE
TRUE
TRUE
FALSE
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, There are NO, NADA commen dates in your example.

There are, however, intersecting date ranges. This is what is being calculated: Just TRUE or FALSE there is at least one intersecting range.

Removing the equality expression returns the number of intersections.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BrianLe]

It is working better in the spreadsheet using "=SUMPRODUCT((B2>=Start)*(A2<=End))>1", but I want to find events that have dates intersecting with events with dates at all 3 sites, not just 1 other site.

I would like a TRUE only if an event occured at at any time
during an event at both of the other 2 sites.

With the above formula, you would get a TRUE if an event occurred at only one of the other 2 sites. It doesn't take into account the site.

In the example, it is coincidental that the times of the events at the 3 sites were such that you get a TRUE for the three events.

Thanks,

Brian

 

[SkipVought]

then maybe change the equality to >2>?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}