Excel Formula - Calculating concurrent phone calls in a call center

Hello,
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.
2. In addition to item #1, be able to see how many concurrent calls at 15 minute intervals within a 24 hour period.

The information I have is (Columns A,B,C) = Date&Call end time/Call Duration (for ex: 2013-02-11, 23:16:15, 6.68)


I'm wondering if someone can help review my formula to check if I got this correctly. This is what I have so far to calculate how many concurrent calls at any given time within a 24 hour period:

=SUMPRODUCT(($A$1:$A$1000=A1)*($B$1:$B$1000=B1)*($C$1:$C$1000>0))

Hi!

How about following VBA code:

Please Login or Register  to view this content.

Function ConcurrentCalls() goes through all calls and checks how many concurrent calls each call has and return max amount of concurrent calls.

If you are unfamiliar with VBA macros, check http://excel4everything.blogspot.fi/...-to-write.html

I haven't tested it :-)

Cheers!