View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Pete_UK Pete_UK is offline
external usenet poster
 
Posts: 8,856
Default Can excel count concurrent events given start and end times ?

Assuming you have call data with start date/time and end date/time
(i.e. start date/time + duration), one way of doing this is to create
TWO records for every call record. One record to contain START date/
time and an indicator set to "S", and the second to contain END date/
time and an indicator set to "E".

Merge the two sets of records together and sort by date/time in
ascending sequence. If the S/E indicator is in column C, and assuming
first record is in row 2, then in column D row 2 add the formula:

=IF(C2="S",D1+1,D1-1)

and replicate this formula downwards to all other records (cell D1
should be initialised to 0). Column D should then contain the number
of simultaneous calls at any one time. You can easily determine the
maximum number (using the MAX function) AND the number of times that
this number, and every lower number, was reached (using the COUNTIF
function).

Hope this helps.

Pete

On Sep 17, 5:56 pm, Charlie B <Charlie
wrote:
Hi there. I have a spreadsheet with thousands of phone call records. Each
has a start time, and end time and of course, therefore, a duration. I want
to get excel to tell me how many concurrent calls there are.

How do I do it ?

Any help greatfully received.

Charlie