Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate events that happened at the same time?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate events that happened at the same time?
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 "Stuboy28" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I calculate events that happened at the same time?
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) "Joel" wrote: 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 "Stuboy28" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
Calculate % of events based on days worked | Excel Worksheet Functions | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
Finding concurrent events from a list with a start and an end time | Excel Discussion (Misc queries) | |||
I need to measure hours between two events(time/date) to give me . | Excel Worksheet Functions |