How can I calculate events that happened at the same time?

[Guest]

Dear all,

I am trying to find a way of calculating how many people were taking a call
at the same time as one and other. I have exported the following data into
Excel and am trying to find a formula which will achieve this:

Column1: Start Time Column2: # of Calls made at Start Time Column 3: Call
Duration Column 4: Finish Time
08:07 1 47.00 8:07
08:11 1 135.00 8:14
08:12 1 92.00 8:13
08:18 1 296.00 8:23
08:24 1 192.00 8:27
08:29 1 83.00 8:31
08:35 1 124.00 8:37
08:36 1 93.00 8:38
08:37 1 170.00 8:40

I would like to also create a chart to display this information?

Thankyou in advance!

 

[Guest]

It is painful . first make a worksheet of each time period
use my function
=GetActivecalls(A1,A$1444:C$1452)
A1 is cell wher first time in table below starts
A$144:C$1452 is the table where each persons calls are tabulated - your table


Table of diffferent time periods
00:00 GetActivecalls(A1,A$1444:C$1452)
00:01 GetActivecalls(A1,A$1444:C$1452)
00:02 GetActivecalls(A2,A$1444:C$1452)

..
..
..
12:00 GetActivecalls(A721,A$1444:C$1452)
12:01 GetActivecalls(A722,A$1444:C$1452)
12:02 GetActivecalls(A723,A$1444:C$1452)

..
..
..
23:58 GetActivecalls(A1,A$1440:C$1452)
23:59 GetActivecalls(A1,A$1440:C$1452)



Function GetActivecalls(MyTime As Date, CallTable As Range)

Dim StartTime As Date
Dim Endtime As Date
Dim EarlyTime As Single
Dim LateTime As Single


myStartRow = CallTable.Row
myStartCol = CallTable.Column
myLastRow = CallTable.End(xlDown).Row
NumberofCalls = 0

For rowOffset = 0 To (myLastRow - myStartRow)

StartTime = Cells(myStartRow, myStartCol).Offset(rowOffset:=rowOffset,
columnoffset:=0)
Endtime = Cells(myStartRow, myStartCol).Offset(rowOffset:=rowOffset,
columnoffset:=3)
StartMinute = Minute(MyTime) - Minute(StartTime)
StartHour = Hour(MyTime) - Hour(StartTime)
EndMinute = Minute(MyTime) - Minute(Endtime)
EndHour = Hour(MyTime) - Hour(Endtime)
If (StartHour >= 0) And (StartMinute >= 0) And _
(EndHour >= 0) And (EndMinute >= 0) Then

NumberofCalls = NumberofCalls + 1
End If
Next rowOffset


GetActivecalls = NumberofCalls

End Function

 

[Guest]

small mistake
these lines need to be changed. Date was wrong

from
StartMinute = Minute(MyTime) - Minute(StartTime)
StartHour = Hour(MyTime) - Hour(StartTime)
EndMinute = Minute(MyTime) - Minute(Endtime)
EndHour = Hour(MyTime) - Hour(Endtime)

to
StartMinute = Minute(MyTime) - Minute(StartTime)
StartHour = Hour(MyTime) - Hour(StartTime)
EndMinute = Minute(Endtime) - Minute(MyTime)
EndHour = Hour(Endtime) - Hour(MyTime)