Calculate the amount of overlapping time

[jameslec]

Good afternoon. I have been looking all over and cannot find an answer on how to do this. I work for a call center that takes inbound technical support calls. We recently added a chat support option for our end users. Our chat agents can take up to 3 chats at the same time. I need to determine the total amount of concurrent chats for each agent per day as well as the total amount of concurrent time for each agent per day. Below is a sample of my data. Notice how the start time on row 5 is earlier than the end time on row 4. I need excel to count this as a concurrent chat and then calculate the amount of time that was concurrent (in this example it would be 16 minutes). Currently I have 2000 rows of data and 30 different agents.

Start_Time End_Time Agent Name (Num) 6/3/13 12:13 6/3/13 12:24 14 6/3/13 12:42 6/3/13 13:04 14 6/5/13 12:07 6/5/13 12:12 14 6/5/13 12:31 6/5/13 13:06 14 6/5/13 12:50 6/5/13 13:08 14 6/5/13 14:18 6/5/13 14:32 14 6/5/13 14:30 6/5/13 14:38 14 6/5/13 14:45 6/5/13 14:53 14 6/5/13 14:59 6/5/13 14:59 14 5/31/13 8:24 5/31/13 8:37 15 5/31/13 11:46 5/31/13 11:53 15 5/31/13 14:00 5/31/13 14:06 15 5/31/13 14:12 5/31/13 14:57 15 5/31/13 14:18 5/31/13 14:56 15 5/31/13 15:14 5/31/13 15:41 15 5/31/13 15:17 5/31/13 15:21 15 5/31/13 15:35 5/31/13 15:43 15 5/31/13 16:02 5/31/13 16:06 15 5/31/13 16:19 5/31/13 16:29 15 5/31/13 16:29 5/31/13 16:36 15 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Vidar]

Re: How to calculate the amount of overlapping time

Try this.
In cell D3 (second call row):
=IF(AND(B2>A3,C2=C3),"Chat","")

In E3 use this:
=IF(D3>"",B2-A3,"")

Drag to copy them down.

 

[jameslec]

Re: How to calculate the amount of overlapping time

Thank you for the quick reply. At first I thought you had nailed it, but after looking further I don't think it is right.
If you look below chat # 4 shows 17.7 min concurrency, but the chat itself was only 8 minutes long. Somehow I think it also needs to look at the end date.

Start_Time	End_Time	Agent Name (Num)	Chat Up	Time Up
5/31/13 8:29:32	5/31/13 8:51:33	14
5/31/13 8:39:05	5/31/13 8:59:12	14	Chat	12.47
5/31/13 8:51:45	5/31/13 9:16:46	14	Chat	7.45
5/31/13 8:59:04	5/31/13 9:07:35	14	Chat	17.70
5/31/13 9:22:22	5/31/13 9:39:12	14
5/31/13 9:32:35	5/31/13 9:39:35	14	Chat	6.62
5/31/13 10:13:44	5/31/13 10:29:50	14
5/31/13 10:37:31	5/31/13 10:58:51	14
6/3/13 12:13:08	6/3/13 12:24:23	14

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Vidar]

Re: How to calculate the amount of overlapping time

ok, this one will choose the lowest value of the overlapping time or the length of the call.
=IF(D3>"",MEDIAN(0,B2-A3,B3-A3),"")

 

[Marcelo Branco]

Re: How to calculate the amount of overlapping time

A possible solution (dates as dd/mm/yyyy)

A B C D E F (headers in row 1)

Start_Time	End_Time	Agent_Num		Agent_Num	Minutes
06/03/2013 12:13	06/03/2013 12:24	14		14	18
06/03/2013 12:42	06/03/2013 13:04	14		15	42
06/05/2013 12:07	06/05/2013 12:12	14		Total	60
06/05/2013 12:31	06/05/2013 13:06	14
06/05/2013 12:50	06/05/2013 13:08	14
06/05/2013 14:18	06/05/2013 14:32	14
06/05/2013 14:30	06/05/2013 14:38	14
06/05/2013 14:45	06/05/2013 14:53	14
06/05/2013 14:59	06/05/2013 14:59	14
31/05/2013 08:24	31/05/2013 08:37	15
31/05/2013 11:46	31/05/2013 11:53	15
31/05/2013 14:00	31/05/2013 14:06	15
31/05/2013 14:12	31/05/2013 14:57	15
31/05/2013 14:18	31/05/2013 14:56	15
31/05/2013 15:14	31/05/2013 15:41	15
31/05/2013 15:17	31/05/2013 15:21	15
31/05/2013 15:35	31/05/2013 15:43	15
31/05/2013 16:02	31/05/2013 16:06	15
31/05/2013 16:19	31/05/2013 16:29	15
31/05/2013 16:29	31/05/2013 16:36	15

<TBODY>
</TBODY>

Formula in F2
=SUMPRODUCT(--($C$2:$C$21=$E2),--($B$2:B$21>$A$3:$A$22),--($A$3:$A$22<>""),($B$2:$B$21-$A$3:$A$22)-($B$2:$B$21-$B$3:$B$22)*($B$2:$B$21>$B$3:$B$22)*($B$3:$B$22<>0))*1440

copy down to F3

M.

 

[jameslec]

Re: How to calculate the amount of overlapping time

Thank you very much for your reply. I am looking at this method as well as Vidar's method. I will play with both and let you know on Monday how it goes. Thank you both.

 

[trizahler]

Re: How to calculate the amount of overlapping time

Hello,

I am working on a similar problem to this and the formula

=SUMPRODUCT(--($C$2:$C$21=$E2),--($B$2:B$21>$A$3:$A$22),--($A$3:$A$22<>""),($B$2:$B$21-$A$3:$A$22)-($B$2:$B$21-$B$3:$B$22)*($B$2:$B$21>$B$3:$B$22)*($B$3:$B$22<>0))*1440

seems to be working for all intents and purposes. I am wondering how to get the cells with overlap to be highlighted ?

Can anyone assist ?

Thank You!

A possible solution (dates as dd/mm/yyyy)

A B C D E F (headers in row 1)

Start_Time	End_Time	Agent_Num		Agent_Num	Minutes
06/03/2013 12:13	06/03/2013 12:24	14		14	18
06/03/2013 12:42	06/03/2013 13:04	14		15	42
06/05/2013 12:07	06/05/2013 12:12	14		Total	60
06/05/2013 12:31	06/05/2013 13:06	14
06/05/2013 12:50	06/05/2013 13:08	14
06/05/2013 14:18	06/05/2013 14:32	14
06/05/2013 14:30	06/05/2013 14:38	14
06/05/2013 14:45	06/05/2013 14:53	14
06/05/2013 14:59	06/05/2013 14:59	14
31/05/2013 08:24	31/05/2013 08:37	15
31/05/2013 11:46	31/05/2013 11:53	15
31/05/2013 14:00	31/05/2013 14:06	15
31/05/2013 14:12	31/05/2013 14:57	15
31/05/2013 14:18	31/05/2013 14:56	15
31/05/2013 15:14	31/05/2013 15:41	15
31/05/2013 15:17	31/05/2013 15:21	15
31/05/2013 15:35	31/05/2013 15:43	15
31/05/2013 16:02	31/05/2013 16:06	15
31/05/2013 16:19	31/05/2013 16:29	15
31/05/2013 16:29	31/05/2013 16:36	15

<tbody>
</tbody>

Formula in F2
=SUMPRODUCT(--($C$2:$C$21=$E2),--($B$2:B$21>$A$3:$A$22),--($A$3:$A$22<>""),($B$2:$B$21-$A$3:$A$22)-($B$2:$B$21-$B$3:$B$22)*($B$2:$B$21>$B$3:$B$22)*($B$3:$B$22<>0))*1440

copy down to F3

M.

 

[Marcelo Branco]

Re: How to calculate the amount of overlapping time

Hello,

I am working on a similar problem to this and the formula

=SUMPRODUCT(--($C$2:$C$21=$E2),--($B$2:B$21>$A$3:$A$22),--($A$3:$A$22<>""),($B$2:$B$21-$A$3:$A$22)-($B$2:$B$21-$B$3:$B$22)*($B$2:$B$21>$B$3:$B$22)*($B$3:$B$22<>0))*1440

seems to be working for all intents and purposes. I am wondering how to get the cells with overlap to be highlighted ?

Can anyone assist ?

Thank You!

Try to provide a small data sample along with expected results.

M.

 

[trizahler]

Re: How to calculate the amount of overlapping timeco

Hello Marcelo, thank you for the reply and my apologies for the late reply. I can provide a google sheet link to my data if that is helpful.

https://docs.google.com/spreadsheets/d/1qAH4EvuuTR6sDe100RYXylJLZ7QAt9HXZOzIJIBnNfo/edit?usp=sharing

I am trying to compare shifts worked by home medical workers (PCA) at different agencies attempting to find overlaps with the formula posted in this forum topic.

Column N ties Columns B, C,D, E together, but I do not know how to expand the formula to tie Columns H,I,J,K together....

Also Column Q is supposed to calculate a total number hours of overlap but I cannot seem to get that formula to work.

Any advice or guidance would be most appreciated.

Thank You for Your Time!

Try to provide a small data sample along with expected results.

M.

 

[Marcelo Branco]

Re: How to calculate the amount of overlapping timeco

I didn't understand exactly what you are trying to do.
(this is an old thread and i don't remember what i did to solve it)

I think you should create a new thread with a small data sample along with expected results. Doing so, others members also can help.

M.