Calculating Overlapping Time

[ashleyshsu1]

I work for a call center and we have agents who take live chats, up to two at a time. I need to calculate the total Active Time without duplicating any time they were on two chats concurrently. I have no idea what kind of formula would help me accomplish this task, but then again my excel skills are very basic. Here's a small sample of my data:

[TABLE="width: 231"]
<tbody>[TR]
[TD="class: xl63, width: 112, bgcolor: transparent"]Chat Start Time
[/TD]
[TD="class: xl63, width: 112, bgcolor: transparent"]Chat End Time
[/TD]
[TD="class: xl63, width: 84, bgcolor: transparent"]Active Time
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:32
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:38
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:06:11
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:35
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:58
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:22:56
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:39
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:48
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:08:37
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:48
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:49
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:01:07
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 7:50
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 8:16
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:25:26
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 10:05
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 10:20
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:15:25
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 10:05
[/TD]
[TD="class: xl65, width: 112, bgcolor: transparent, align: right"]1/4/2018 10:22
[/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]0:16:59
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 112, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 112, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 84, bgcolor: transparent, align: right"]1:36:41
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Grizzly Adams]

I’m not exactly sure I understand what it is you need. Could u give an example. I will try my best to help