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:

Function max(a, b)
If a > b Then
    max = a
Else
    max = b
End If

End Function
Function min(a, b)
If a > b Then
    min = b
Else
    min = a
End If

End Function
Function isConcurrent(start1, end1, start2, end2)
    If max(start1, start2) <= min(end1, end2) Then
        isConcurrent = True
    Else
        isConcurrent = False
    End If
End Function

Function ConcurrentCalls()
    maxconcurrent = 1
    r1 = 2
    Do        
        concurrent = 1
        r2 = 2
        Do
            start1 = Cells(r1, 1).Value + Cells(r1, 2).Value
            end1 = start1 + Cells(r1, 3).Value
            start2 = Cells(r2, 1).Value + Cells(r2, 2).Value
            end2 = start2 + Cells(r2, 3).Value
            If r1 <> r2 And isConcurrent(start1, end1, start2, end2) Then
                concurrent = concurrent + 1
            End If
            r2 = r2 + 1
        Loop Until Cells(r2, 1) = ""
        maxconcurrent = max(maxconcurrent, concurrent)
          
    Loop Until Cells(r1, 1) <> ""
    ConcurrentCalls = maxconcurrent
End Function

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!