Expert: Miguel Zapico - 3/25/2008

Question
Hi

I like to evaluate how many unique overlapping times i have in my table any second or minute.
Here is a extract of the table that would show 1 unique overlap out of 3 records:

ID   Start Time               EndTime
100  10/01/2008  4:29:00 p.m. 10/01/2008  4:31:05 p.m.
101  10/01/2008  4:30:00 p.m. 10/01/2008  4:35:00 p.m.
102  10/01/2008  4:36:14 p.m. 10/01/2008  4:38:03 p.m.

Any help is greatly appreciated
Andy

Answer
I am assuming that the data will be ordered by Start Time, so we can use formulas for the solution.  One way of finding the overlaps is with an additional column, with a formula like:
=IF(B3>0,C2>B3)
This should go in D2, and then drag the formula across the column.  To get the total figures, you can use a formula like:
=COUNTIF(D:D,TRUE)
And if you use an autofilter (menu Data->Filter->Autofilter) you can filter by TRUE values on that column to show the overlapping lines.

If you just want the number of overlaps, and you don't want to use an extra column, you can use an array formula like:
=SUM(--(C2:C99>B3:B100))
Enter it with Crtl+Shift+Enter.  The ranges must have the same number of elements, and they should reflect the number of existing rows (up to row 100 in this case).

Hope this helps,
Miguel