Calculate interval between phone calls for multiple agents in one column

Jaffey · Dec 1st 2016

I need to calculate the interval between phone calls for agents working in a call centre.

Example: An agent places a phone call at 10:00 AM with a duration of 3 minutes. They make their next call at 10:10 AM. The time between the end of the first call and the beginning of the second call equals an interval of 7 minutes. This is a straightforward calculation when dealing with call records for just one agent but more difficult (for me) when the data contains call records for multiple agents combined. My attempts using VLOOkUP and INDEX(MATCH have been unsuccessful thus far. Any assistance is much appreciated!

I have attached a sample sheet of some data, which is also pictured below. I have highlighted the formula which subtracts the start time of the previous call from the current call, less the duration of the previous call, to correctly arrive at the interval between calls, however, the formula does not account for the name of the agent so I have to adjust it manually for each call record which is what I'm trying to avoid.

[ATTACH=CONFIG]70762[/ATTACH]

forum.ozgrid.com/index.php?attachment/70755/

GOLDENEXCEL · Dec 12th 2016

Re: Calculate interval between phone calls for multiple agents in one column

Hi Jaffey,

Solution for you:

Firstly you have to insert one more column at A column and then past below formulas in cell A2 and drag down:

=IF(D2="","",D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(COUNTIFS($B$1:B2,B2,$D$1:D2,D2),"00#"))

Next you have to past one more formula at cell G2 and drag down in order to get the result you want:

=IF(A2="","",IF(RIGHT(A2,3)="001","First Call for "&TEXT(B2,"dd/MM/yyyy"),C2-(VLOOKUP((D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(VALUE(RIGHT(A2,3)-1),"00#")),$A:$C,3,FALSE)+VLOOKUP((D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(VALUE(RIGHT(A2,3)-1),"00#")),$A:$F,6,FALSE))))

Cheer!!!

Calculate interval between phone calls for multiple agents in one column

Participate now!

Share