Concurrent Calls

[Andrew]

I have a worksheet which contains the time a call came in and the time it
ended. What I am trying to find out is for each one hour period during a
day (or even better, what 30 minute period), what was the peak (maximum)
number of callers at any one time and what time (if possible, in 5 second
increments), were there the most concurrent callers.

To set this up, I have one spreadsheet with the values of the date(38109 =
05/02/04)

formatted date date
05/02/04 12:00:35 PM 38109.500405083700
05/02/04 12:00:40 PM 38109.500462954100
05/02/04 12:00:45 PM 38109.500520824500
05/02/04 12:00:50 PM 38109.500578694800
etc. in 5 second increments

On the other worksheet I have the call records:
call_start call_end
38109.50041 38109.5024
38109.50431 38109.5048
38109.5055 38109.5125
....etc

So for this example, the peak number of callers would be 2 at 12:00:35 PM
(38109.500405..)

Any strategies you can pass along would be of great help. Is this something
that I can only solve using ExcelVBA or is there an array formula that can
handle this?

Sorry for the cross-post, but just want to cover my bases with finding a
solution.

-Andrew

 

[Frank Kabel]

Hi
try the following:
Assumptions:
- sheet 1, col. a contains your date values starting in row 1
- sheet 2, col A contains the call start and col. B the call end.

Enter the following formula in cell B1 on sheet 1:
=SUMPRODUCT(--('sheet2'!$A$1:$A$1000>=A1),--('sheet2!$A$1:$A$1000<A2))
this counts all calls STARTED in your 5 second range. Copy this formula
down for all rows