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>

 

[trizahler]

Re: How to calculate the amount of overlapping timeco

Thank you Marcelo, I appreciate your follow-up!

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.

 

[Marcelo Branco]

Re: How to calculate the amount of overlapping timeco

Thank you Marcelo, I appreciate your follow-up!

You are welcome.
Sorry for not providing a solution to your problem - i took a look at your data , but could not understand what you really need.
Maybe, fresh eyes. That's why i'm suggesting to create a new thread.

M.