Find duplicate / overlap (-ping) dates in data

[samcdavies]

I have a list of date ranges, eg;

1 Jan 12 - 3 Jan 12
5 Feb 12 - 7 Feb 12
9 Mar 12 - 9 Mar 12
1 June 12 - 7 June 12
3 June 12 - 3 June 12

Is there any formula which can alert me to overlaps in data - eg;

1 June 12 - 7 June 12
3 June 12 - 3 June 12

Thanks in advance for any help offered

[Russell Dawson]

Hi

If your data is in columns A1:B5 in C2 put

Please Login or Register  to view this content.

and copy down

[samcdavies]

Hi..

Hi thanks for the answer. I should have mentioned that the data isn't always in date order. Is there a solution that looks at all data and compares?

[Russell Dawson]

Tricky.

The best I can come up with so far is this but it only can match exact dates in the columns.

Please Login or Register  to view this content.

We need to extend that to include the range of dates between A & B columns and report any matches, and the dates are not in order except that in its own rows Col A is always before Col B date.

[zbor]

Maybe:

=SUMPRODUCT(--($A$1:$A$1000<A1),--($B$1:$B$1000>B1))