Find how many agents worked how long on a call

[cr2crf]

Hello

I need to know how many agents worked on a call and for how long. The example below starts with column A and ends with F, rows 1 thru 6. The example is very simplified as there may be as many as 6 different agents helping on a call. Variables are, each time an agent logs into the call to add something the start time stays the same as when the call started but the end time changes to equal the time they ended their comment. When they make another comment, they get the same start time with a new end time. The total call duration equals the original call start time and the last end time noted by one of the agents.

If my life was a simple as the attached I could hand count this but I have 117 agents working 24/7 taking 1200 calls per day. Ugh!

Any help would be appreciated.

Thanks


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AGENT[/TD]
[TD]CALL ID[/TD]
[TD]START DT[/TD]
[TD]START TM[/TD]
[TD]END DT[/TD]
[TD]END TM[/TD]
[/TR]
[TR]
[TD]JOE[/TD]
[TD]1010[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:49AM[/TD]
[TD]1/1/2018[/TD]
[TD]1:21:06AM[/TD]
[/TR]
[TR]
[TD]JOE[/TD]
[TD]1010[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:49AM[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:52AM[/TD]
[/TR]
[TR]
[TD]BILL[/TD]
[TD]1010[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:49AM[/TD]
[TD]1/1/2018[/TD]
[TD]12:12:53AM[/TD]
[/TR]
[TR]
[TD]BILL[/TD]
[TD]1010[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:49AM[/TD]
[TD]1/1/2018[/TD]
[TD]12:30:03AM[/TD]
[/TR]
[TR]
[TD]ALLEN[/TD]
[TD]1010[/TD]
[TD]1/1/2018[/TD]
[TD]12:07:49AM[/TD]
[TD]1/1/2018[/TD]
[TD]12:47:53AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

If I understand your requirement correctly, I think you can achieve it with a pivot table … You need to place a "space" between the time & AM or PM so excel can read the time correctly

[TABLE="width: 409"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of AGENT[/TD]
[TD]Min of START TM[/TD]
[TD]Max of END TM[/TD]
[/TR]
[TR]
[TD]1010[/TD]
[TD] 5.00[/TD]
[TD="align: right"]12:07 AM[/TD]
[TD="align: right"]1:21 AM[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 5.00[/TD]
[TD="align: right"]12:07 AM[/TD]
[TD="align: right"]1:21 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[cr2crf]

Is there a formula that will separate the AM and PM? I have thousands of lines. To do it manually would take days./

 

[mse330]

This is one way to do it ...

Book1
	A	B
1	END TM
2	1:21:06PM	1:21 PM
Sheet1

Cell Formulas
Range	Formula
B2	=TIMEVALUE(IF(RIGHT(A2,2)="AM",SUBSTITUTE(A2,"AM"," AM"),SUBSTITUTE(A2,"PM"," PM")))

 

[cr2crf]

That worked for the time, however in your example it counted five agents when there were only three. there has to be a formula that can do it. Anyone?

 

[mse330]

If you are using excel 2013 or above, you can get distinct count in pivot table - Just check the following link
https://www.exceltrick.com/how_to/count-unique-values-excel/ … Scroll to the middle of the page to "Method 3"

Book1
	A	B	C	D
3	Row Labels	Distinct Count of AGENT	Min of START TM	Max of END TM
4	1010	3.00	12:07 AM	1:21 AM
5	Grand Total	3.00	12:07 AM	1:21 AM
Sheet2

 

[mse330]

That worked for the time, however in your example it counted five agents when there were only three. there has to be a formula that can do it. Anyone?

Here's a UDF formula to count based on your need, maybe not the best effecient way but it should do the job

	A	B	C	D	E	F	G
AGENT	CALL ID	START DT	START TM	END DT	END TM	# of Agents
JOE	12:07:49AM	1:21:06AM
JOE	12:07:49AM	12:07:52AM
BILL	12:07:49AM	12:12:53AM
BILL	12:07:49AM	12:30:03AM
ALLEN	12:07:49AM	12:47:53AM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1010[/TD]
[TD="align: right"]01-01-18[/TD]

[TD="align: right"]01-01-18[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1010[/TD]
[TD="align: right"]01-01-18[/TD]

[TD="align: right"]01-01-18[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1010[/TD]
[TD="align: right"]01-01-18[/TD]

[TD="align: right"]01-01-18[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1010[/TD]
[TD="align: right"]01-01-18[/TD]

[TD="align: right"]01-01-18[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1010[/TD]
[TD="align: right"]01-01-18[/TD]

[TD="align: right"]01-01-18[/TD]

[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=CountUnique($A$2:A6,$B$2:B6,B2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=CountUnique($A$2:A7,$B$2:B7,B3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=CountUnique($A$2:A8,$B$2:B8,B4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=CountUnique($A$2:A9,$B$2:B9,B5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=CountUnique($A$2:A10,$B$2:B10,B6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Function CountUnique(Rg As Range, IDs_Rg As Range, ID As String) As Double

Dim Arr() As Variant
Arr = Union(Rg, IDs_Rg)

For x = LBound(Arr) To UBound(Arr)
    If Arr(x, 2) <> ID Then Arr(x, 1) = 0
Next x

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

For x = LBound(Arr) To UBound(Arr)
    If Arr(x, 1) <> 0 Then
        If Not dict.Exists(Arr(x, 1)) Then dict.Add key:=Arr(x, 1), Item:=0
    End If
Next x

CountUnique = dict.Count

End Function