Excel VBA macro example: How to count concurrent times

Here is an example VBA macro for solving a call log analysis problem:

We have call log

• Column A: Date of the call
• Column B: Time of the call
• Column C: Duration of the call

How many concurrent calls?

Following screen capture demonstrates the situation:

To solve the problem I implemented Visual Basic macro ConcurrentCalls() and helper functions max(), min() and isConcurrent(). Here is the 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
  Els
    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

Feel free to examine and let me know if you see a bug or can imagine a better solution!

Check also 101 Excel Secrets and Everyday Excel video course.