Calculating concurrent calls in a call center

**achooi** · 02-11-2013, 08:26 PM

Hello,
I'm editing the original post because I didn't include a dummy spreadsheet and enough details. My request was too complicated and I'm looking for a simpler solution. Hopefully someone can help me on this one. My goal is to:

1. Calculate how many concurrent calls a call center has at any given time within a 24 hour period. If at least two calls overlap in start & end time, then it should be counted as two concurrent calls.

I have 3 columns of data: Date, End time, and call duration in seconds. (this is all I have to work with)

I've tried using hamjam's code but I don't have enough expertise or the required information to make it work.

Reason for this spreadsheet: I have a VoIP service provider that limits the number of concurrent calls I can make. I need to determine if more capacity needs to be purchased.

**jroques** · 02-11-2013, 11:41 PM

Your formula is right if the calls end at the same time. The problem that I see in your formula is when you have a call does not end at the same time and yet where answered in the same lapse of time.

I need to ask you if a call start at 12:01 and ends at 12:06 and another call start 12:02 and ends at 12:10, do you consider this two concurrent calls?

Javier

**FDibbins** · 02-12-2013, 01:47 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**hamjam** · 02-12-2013, 08:44 AM

Hello
You have a very short formula for a relatively long problem.

To simplify it, can we agree to break up the 24 hour cycle into the 15 minute intervals ? This would create 4 x 24 = 96 time blocks. I would then determine which block each call record would fall into, some into multiple.

At the end it is a matter of summing up the number of calls per block to give your concurrent calls.

Do you want to give it a try ? You could run it through formula without VBA and being familiar with array formula, you could probably manage that. If you get stuck, shout !

Regards

**hamjam** · 02-12-2013, 08:48 AM

duplicate, rest deleted

**hamjam** · 02-14-2013, 08:28 AM

Hello Achooi

There does not seem to have been much more activity in this thread.
So continuing with my line of thought, I have realised it is a little more complicated. I have written a formula for determine the start time :

Please Login or Register  to view this content.

And added a few more columns to the spreadsheet that was looking pretty thin to begin with. The new columns determine which timebox each call fall into, some fall into multiple timeboxes. The totals row then provides you with the total concurrent calls for each timeboxes.

If you wanted finer granularity, you could break up the timeboxes into smaller durations, say 5 minutes each to give you 24 * 60 / 5 = 288 elements. This becomes rather messy with formula and easier with VBA, but I got the sense that you would prefer to stick within formula.

Regards

**achooi** · 02-19-2013, 07:21 PM

I've edited the original post.

**achooi** · 02-19-2013, 07:49 PM

Hello hamjam, after I add my own start and end time in your spreadsheet, I'm getting all kinds of errors. I've actually edited my original post for a much simpler solution and along with a dummy sheet.