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.

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

 

[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.