Calculate the amount of overlapping time

J

jameslec

New Member
Joined
Jun 21, 2013
Messages
10
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]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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.
 
Upvote 0
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]
 
Upvote 0
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),"")
 
Upvote 0
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.

 
Last edited:
Upvote 0
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.
 
Upvote 0
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!



Marcelo Branco said:
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.
Click to expand...
 
Upvote 0
Re: How to calculate the amount of overlapping time

trizahler said:
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!
Click to expand...

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

M.
 
Upvote 0
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!

Marcelo Branco said:
Try to provide a small data sample along with expected results.

M.
Click to expand...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
List of items within a specific month
Replies
4
Views
201
Fluff
Fluff
S
  • Solved
Individual Ranking
2
Replies
11
Views
433
sksanjeev786
S
A
  • Solved
Count entry by time
Replies
3
Views
168
admvlad
A
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
368
Eric W
Eric W

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top