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]

 

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

[TABLE="width: 479"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Start_Time[/TD]
[TD]End_Time[/TD]
[TD]Agent Name (Num)[/TD]
[TD]Chat Up[/TD]
[TD]Time Up[/TD]
[/TR]
[TR]
[TD]5/31/13 8:29:32[/TD]
[TD]5/31/13 8:51:33[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/13 8:39:05[/TD]
[TD]5/31/13 8:59:12[/TD]
[TD]14[/TD]
[TD]Chat[/TD]
[TD="align: right"]12.47[/TD]
[/TR]
[TR]
[TD]5/31/13 8:51:45[/TD]
[TD]5/31/13 9:16:46[/TD]
[TD]14[/TD]
[TD]Chat[/TD]
[TD="align: right"]7.45[/TD]
[/TR]
[TR]
[TD]5/31/13 8:59:04[/TD]
[TD]5/31/13 9:07:35[/TD]
[TD]14[/TD]
[TD]Chat[/TD]
[TD="align: right"]17.70[/TD]
[/TR]
[TR]
[TD]5/31/13 9:22:22[/TD]
[TD]5/31/13 9:39:12[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/13 9:32:35[/TD]
[TD]5/31/13 9:39:35[/TD]
[TD]14[/TD]
[TD]Chat[/TD]
[TD="align: right"]6.62[/TD]
[/TR]
[TR]
[TD]5/31/13 10:13:44[/TD]
[TD]5/31/13 10:29:50[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/13 10:37:31[/TD]
[TD]5/31/13 10:58:51[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/3/13 12:13:08[/TD]
[TD]6/3/13 12:24:23[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 800"]
<TBODY>[TR]
[TD="width: 145, bgcolor: transparent"]Start_Time
[/TD]
[TD="width: 145, bgcolor: transparent"]End_Time
[/TD]
[TD="width: 81, bgcolor: transparent"]Agent_Num
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]Agent_Num
[/TD]
[TD="width: 64, bgcolor: transparent"]Minutes
[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/03/2013 12:13
[/TD]
[TD="width: 145, bgcolor: white"]06/03/2013 12:24
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/03/2013 12:42
[/TD]
[TD="width: 145, bgcolor: transparent"]06/03/2013 13:04
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: transparent"]06/05/2013 12:07
[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 12:12
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="bgcolor: transparent, align: right"]60
[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 12:31
[/TD]
[TD="width: 145, bgcolor: yellow"]06/05/2013 13:06
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: yellow"]06/05/2013 12:50
[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 13:08
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:18
[/TD]
[TD="width: 145, bgcolor: yellow"]06/05/2013 14:32
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: yellow"]06/05/2013 14:30
[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:38
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:45
[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:53
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:59
[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:59
[/TD]
[TD="width: 81, bgcolor: white"]14
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 08:24
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 08:37
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 11:46
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 11:53
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 14:00
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 14:06
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 14:12
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 14:57
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 14:18
[/TD]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 14:56
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 15:14
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 15:41
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 15:17
[/TD]
[TD="width: 145, bgcolor: #8db4e2"]31/05/2013 15:21
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 15:35
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 15:43
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:02
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:06
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:19
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:29
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:29
[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:36
[/TD]
[TD="width: 81, bgcolor: white"]15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


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)

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 145, bgcolor: transparent"]Start_Time[/TD]
[TD="width: 145, bgcolor: transparent"]End_Time[/TD]
[TD="width: 81, bgcolor: transparent"]Agent_Num[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]Agent_Num[/TD]
[TD="width: 64, bgcolor: transparent"]Minutes[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/03/2013 12:13[/TD]
[TD="width: 145, bgcolor: white"]06/03/2013 12:24[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/03/2013 12:42[/TD]
[TD="width: 145, bgcolor: transparent"]06/03/2013 13:04[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: transparent"]06/05/2013 12:07[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 12:12[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent, align: right"]60[/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 12:31[/TD]
[TD="width: 145, bgcolor: yellow"]06/05/2013 13:06[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: yellow"]06/05/2013 12:50[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 13:08[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:18[/TD]
[TD="width: 145, bgcolor: yellow"]06/05/2013 14:32[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: yellow"]06/05/2013 14:30[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:38[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:45[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:53[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]06/05/2013 14:59[/TD]
[TD="width: 145, bgcolor: white"]06/05/2013 14:59[/TD]
[TD="width: 81, bgcolor: white"]14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 08:24[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 08:37[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 11:46[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 11:53[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 14:00[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 14:06[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 14:12[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 14:57[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 14:18[/TD]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 14:56[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 15:14[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 15:41[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: #b8cce4"]31/05/2013 15:17[/TD]
[TD="width: 145, bgcolor: #8db4e2"]31/05/2013 15:21[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 15:35[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 15:43[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:02[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:06[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:19[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:29[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 145, bgcolor: white"]31/05/2013 16:29[/TD]
[TD="width: 145, bgcolor: white"]31/05/2013 16:36[/TD]
[TD="width: 81, bgcolor: white"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


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.