Thread: Count the number of instances of a date & time within an array of dates and times

All,

Having trouble figuring this one out...

I am trying to take a date and time and see how many times that date and time overlapped with other instances of that date and time. I have converted all times to military time.

Example:


--A--B--C--D--
--Start Date--Start Time--End Date--End Time--

ROW1--2/19/2010--9:00--2/19/2010--10:55--
ROW2--2/19/2010--9:03--2/19/2010--10:10--
ROW3--2/19/2010--10:01--2/19/2010--10:25--
ROW4--2/19/2010--10:26--2/19/2010--11:00--

If we look at the above example, the 3rd entry overlaps with the entire data set 2 times. I need a formula to count this in a large spreadsheet for all entries above and below each entry, for every entry. So an example result for each entry would be:

--A--B--C--D--E--
--Start Date--Start Time--End Date--End Time--# of times overlap--

ROW1--2/19/2010--9:00--2/19/2010--10:55--3
ROW2--2/19/2010--9:03--2/19/2010--10:10--2
ROW3--2/19/2010--10:01--2/19/2010--10:25--2
ROW4--2/19/2010--10:26--2/19/2010--11:00--1

Any Help on this would be greatly appreciated!

Why are rows 2 & 3 supposed to return 2, I would have thought it should be 1?

Because if ROW2's meeting starts at 9:30 and ends at 10:10, it overlaps ROW1's meeting that started at 9:00 and ends at 10:55 and ROW3's meeting that started at 10:01 and ended at 10:25.

Likewise, ROW3's meeting starts at 10:01 and ends at 10:25, it overlaps with ROW1's meeting that starts at 9:00 and ended at 10:55 and with ROW2's meeting that started at 9:03 and ended at 10:10.

Even if it only overlaps for 1 second, I need to track it. These are web conference meetings and I am trying to determine the maximum amount of peak concurrent usage over the entire date range of data.