Over lapping time calculation for same entries

M

meowbhow

New Member
Joined
May 31, 2018
Messages
21
I need the overlapping time in column D, for example, A2, A7 and A8 has keyword KUND (USF). If there is any common time between these three entries then we should mention it in column D

[TABLE="class: cms_table, width: 527"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlapping Time[/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:22 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Janah (USF) Khuzdar[/TD]
[TD="align: right"]7:27:23 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:24 PM[/TD]
[TD="align: right"]9:21:24 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:00:25 PM[/TD]
[TD="align: right"]10:12:25 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD="align: right"]7:27:50 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:27:57 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD="align: right"]7:28:03 PM[/TD]
[TD="align: right"]9:27:22 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD="align: right"]7:28:06 PM[/TD]
[TD="align: right"]8:27:22 PM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The formula in D2 must be confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Follow these steps
Select D2

Paste the formula below in the Formula Bar
=IF(A2=F$2,SUM(IF(A$2:A$9=A2,IF(1-((B2>C$2:C$9)+(B$2:B$9>C2)),IF(ROW(A$2:A$9)>ROW(A2),IF(C$2:C$9>C2,C2,C$2:C$9)-IF(B$2:B$9>B2,B$2:B$9,B2))))),"")

With the cursor inside the Formula Bar Keep the keys Ctrl and Shift pressed
Hit Enter

Excel automatically wraps the formula with curly-braces
{=IF(A2=F$2,SUM(IF(A$2:A$9=A2,IF(1-((B2>C$2:C$9)+(B$2:B$9>C2)),IF(ROW(A$2:A$9)>ROW(A2),IF(C$2:C$9>C2,C2,C$2:C$9)-IF(B$2:B$9>B2,B$2:B$9,B2))))),"")}

Copy (drag) the formula down until D9

The formulas in D2:D9 should return the results i've showed above.

M.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Still some issues the overallping time should be 59 minutes instead of 49 minutes :
[TABLE="width: 765"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlapping Time[/TD]
[TD]Is Overlapping[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD]7:27:22 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD]0:49:55[/TD]
[TD]TRUE[/TD]
[TD]Kund (USF) Khuzdar[/TD]
[/TR]
[TR]
[TD]Janah (USF) Khuzdar[/TD]
[TD]7:27:23 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD] [/TD]
[TD]FALSE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD]7:27:24 PM[/TD]
[TD]9:21:24 PM[/TD]
[TD] [/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD]7:00:25 PM[/TD]
[TD]10:12:25 PM[/TD]
[TD] [/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD]7:27:50 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD] [/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD]7:37:27 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD]0:00:00[/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7:28:03 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD] [/TD]
[TD]FALSE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD]7:28:06 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD] [/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
For larger data the values are coming in negative, please recheck the logic.
[TABLE="width: 1224"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Site name updated[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Site Name[/TD]
[TD]Criteria[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:52[/TD]
[TD="align: right"]2017-11-08 02:29:02[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar (3G-S-4521)[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:54[/TD]
[TD="align: right"]2017-11-08 02:28:54[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar (3G-S-4525)[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:56[/TD]
[TD="align: right"]2017-11-08 02:28:36[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar (3G-S-4485)[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]0:49:27[/TD]
[/TR]
[TR]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:57[/TD]
[TD="align: right"]2017-11-08 02:28:27[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar (3G-S-4522)[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:36:23[/TD]
[TD="align: right"]2017-11-08 02:25:55[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar (S-3725)[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:36:31[/TD]
[TD="align: right"]2017-11-08 02:25:58[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar (S-3688)[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:36:31[/TD]
[TD="align: right"]2017-11-08 02:25:57[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar (S-3724)[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:36:37[/TD]
[TD="align: right"]2017-11-08 02:25:56[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar (S-3728)[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: right"]2017-11-08 04:49:48[/TD]
[TD="align: right"]2017-11-08 05:21:47[/TD]
[TD]Torani (USF) Khuzdar (S-3746)[/TD]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: right"]2017-11-08 04:49:51[/TD]
[TD="align: right"]2017-11-08 05:21:36[/TD]
[TD]Torani (USF) Khuzdar (3G-S-4543)[/TD]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:34:59[/TD]
[TD="align: right"]2017-11-08 05:42:59[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar (3G-S-4525)[/TD]
[TD]Surkarodi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:02[/TD]
[TD="align: right"]2017-11-08 05:42:47[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar (3G-S-4522)[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:02[/TD]
[TD="align: right"]2017-11-08 05:42:47[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar (3G-S-4521)[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:01[/TD]
[TD="align: right"]2017-11-08 05:42:41[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar (3G-S-4485)[/TD]
[TD]Bakijo (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]0:06:57[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:28[/TD]
[TD="align: right"]2017-11-08 05:42:49[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar (S-3724)[/TD]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
[TR]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:27[/TD]
[TD="align: right"]2017-11-08 05:42:46[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar (S-3725)[/TD]
[TD]Guzgi (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: center"]#################[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
The issue is that we have to put a check on DATE too. For different dates the formula is giving garbage values:
[TABLE="width: 637"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Site name updated[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlaping Time[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:52[/TD]
[TD="align: right"]2017-11-08 02:29:02[/TD]
[TD="align: right"]-86177.56[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:28[/TD]
[TD="align: right"]2017-11-08 05:42:49[/TD]
[TD="align: right"]-86177.59[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-13 06:47:02[/TD]
[TD="align: right"]2017-11-13 07:05:42[/TD]
[TD="align: right"]-86177.58[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
meowbhow said:
Still some issues the overallping time should be 59 minutes instead of 49 minutes :
[TABLE="width: 765"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlapping Time[/TD]
[TD]Is Overlapping[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD]7:27:22 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD]0:49:55[/TD]
[TD]TRUE[/TD]
[TD]Kund (USF) Khuzdar[/TD]
[/TR]
[TR]
[TD]Janah (USF) Khuzdar[/TD]
[TD]7:27:23 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD]7:27:24 PM[/TD]
[TD]9:21:24 PM[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD]7:00:25 PM[/TD]
[TD]10:12:25 PM[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hitachi (USF) Khuzdar[/TD]
[TD]7:27:50 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kund (USF) Khuzdar[/TD]
[TD]7:37:27 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD]0:00:00[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7:28:03 PM[/TD]
[TD]9:27:22 PM[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Herok (USF) Khuzdar[/TD]
[TD]7:28:06 PM[/TD]
[TD]8:27:22 PM[/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

For me the result is correct
From 7:37:27 PM to 8:27:22 PM the overlap is 0:49:55 (49 minutes and 55 seconds)

M.
 
Upvote 0
Marcelo, pls do one last favour. Please check the date issue. I am not getting single accurate value because of this.
[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD]Site name updated
[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlaping Time[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar
[/TD]
[TD="align: right"]2017-11-08 01:35:52[/TD]
[TD="align: right"]2017-11-08 02:29:02[/TD]
[TD="align: right"]-86177.56[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar
[/TD]
[TD="align: right"]2017-11-08 05:35:28[/TD]
[TD="align: right"]2017-11-08 05:42:49[/TD]
[TD="align: right"]-86177.59[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar
[/TD]
[TD="align: right"]2017-11-13 06:47:02[/TD]
[TD="align: right"]2017-11-13 07:05:42[/TD]
[TD="align: right"]-86177.58[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
See if this is what you need


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Site name updated​
[/TD]
[TD]
Start Time​
[/TD]
[TD]
End Time​
[/TD]
[TD]
Result​
[/TD]
[TD][/TD]
[TD]
Site​
[/TD]
[TD]
Total​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35​
[/TD]
[TD]
08/11/2017 02:29​
[/TD]
[TD]
00:49:26​
[/TD]
[TD][/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:56:45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35​
[/TD]
[TD]
08/11/2017 02:28​
[/TD]
[TD]
00:49:19​
[/TD]
[TD][/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:49:19​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35​
[/TD]
[TD]
08/11/2017 02:28​
[/TD]
[TD]
00:49:27​
[/TD]
[TD][/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:49:27​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35​
[/TD]
[TD]
08/11/2017 02:28​
[/TD]
[TD]
00:49:32​
[/TD]
[TD][/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:56:51​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36​
[/TD]
[TD]
08/11/2017 02:25​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
00:31:45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36​
[/TD]
[TD]
08/11/2017 02:25​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36​
[/TD]
[TD]
08/11/2017 02:25​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36​
[/TD]
[TD]
08/11/2017 02:25​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
08/11/2017 04:49​
[/TD]
[TD]
08/11/2017 05:21​
[/TD]
[TD]
00:31:45​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
08/11/2017 04:49​
[/TD]
[TD]
08/11/2017 05:21​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:34​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:07:19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:07:19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35​
[/TD]
[TD]
08/11/2017 05:42​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D2 copied down
=SUM(IF(A$2:A$17=A2,IF(1-((B2>C$2:C$17)+(B$2:B$17>C2)),IF(ROW(A$2:A$17)>ROW(A2),IF(C$2:C$17>C2,C2,C$2:C$17)-IF(B$2:B$17>B2,B$2:B$17,B2)))))
Ctrl+Shift+Enter

Create a unique list of sites (paste A2:A17 in F2:F17; select the entire list; Data > Remove Duplicates)
Formula in G2 copied down
=SUMIF(A$2:A$17,F2,D$2:D$17)

M.
 
Upvote 0
meowbhow said:
Marcelo, pls do one last favour. Please check the date issue. I am not getting single accurate value because of this.
[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD]Site name updated[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Overlaping Time[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 01:35:52[/TD]
[TD="align: right"]2017-11-08 02:29:02[/TD]
[TD="align: right"]-86177.56[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-08 05:35:28[/TD]
[TD="align: right"]2017-11-08 05:42:49[/TD]
[TD="align: right"]-86177.59[/TD]
[/TR]
[TR]
[TD]Killi Abdul (USF) (Wateen OFS) Khuzdar[/TD]
[TD="align: right"]2017-11-13 06:47:02[/TD]
[TD="align: right"]2017-11-13 07:05:42[/TD]
[TD="align: right"]-86177.58[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

No overlaps in this case


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Site name updated​
[/td][td]
Start Time​
[/td][td]
End Time​
[/td][td]
Overlaping Time​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/td][td]
08/11/2017 01:35​
[/td][td]
08/11/2017 02:29​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/td][td]
08/11/2017 05:35​
[/td][td]
08/11/2017 05:42​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/td][td]
13/11/2017 06:47​
[/td][td]
13/11/2017 07:05​
[/td][td]
0​
[/td][/tr]
[/table]

Array formula in D2 copied down
=SUM(IF(A$2:A$4=A2,IF(1-((B2>C$2:C$4)+(B$2:B$4>C2)),IF(ROW(A$2:A$4)>ROW(A2),IF(C$2:C$4>C2,C2,C$2:C$4)-IF(B$2:B$4>B2,B$2:B$4,B2)))))
Ctrl+Shift+Enter

M.
 
Upvote 0
The data in post 18 is not correct - missing the seconds in columns B:C

This is Ok


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Site name updated​
[/TD]
[TD]
Start Time​
[/TD]
[TD]
End Time​
[/TD]
[TD]
Result​
[/TD]
[TD][/TD]
[TD]
Site​
[/TD]
[TD]
Total​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35:52​
[/TD]
[TD]
08/11/2017 02:29:02​
[/TD]
[TD]
00:49:26​
[/TD]
[TD][/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:56:45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35:54​
[/TD]
[TD]
08/11/2017 02:28:54​
[/TD]
[TD]
00:49:19​
[/TD]
[TD][/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:49:19​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35:56​
[/TD]
[TD]
08/11/2017 02:28:36​
[/TD]
[TD]
00:49:27​
[/TD]
[TD][/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:49:27​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:35:57​
[/TD]
[TD]
08/11/2017 02:28:27​
[/TD]
[TD]
00:49:32​
[/TD]
[TD][/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
00:56:51​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36:23​
[/TD]
[TD]
08/11/2017 02:25:55​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
00:31:45​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36:31​
[/TD]
[TD]
08/11/2017 02:25:58​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36:31​
[/TD]
[TD]
08/11/2017 02:25:57​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 01:36:37​
[/TD]
[TD]
08/11/2017 02:25:56​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
08/11/2017 04:49:48​
[/TD]
[TD]
08/11/2017 05:21:47​
[/TD]
[TD]
00:31:45​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Torani (USF) Khuzdar​
[/TD]
[TD]
08/11/2017 04:49:51​
[/TD]
[TD]
08/11/2017 05:21:36​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Surkarodi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:34:59​
[/TD]
[TD]
08/11/2017 05:42:59​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35:02​
[/TD]
[TD]
08/11/2017 05:42:47​
[/TD]
[TD]
00:07:19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35:02​
[/TD]
[TD]
08/11/2017 05:42:47​
[/TD]
[TD]
00:07:19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
Bakijo (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35:01​
[/TD]
[TD]
08/11/2017 05:42:41​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
Killi Abdul (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35:28​
[/TD]
[TD]
08/11/2017 05:42:49​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
Guzgi (USF) (Wateen OFS) Khuzdar​
[/TD]
[TD]
08/11/2017 05:35:27​
[/TD]
[TD]
08/11/2017 05:42:46​
[/TD]
[TD]
00:00:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Use the formulas in post 18

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Solved
Let formula?
2
Replies
12
Views
464
Lacan
Lacan
S
  • Solved
Average in excel
Replies
3
Views
257
etaf
etaf
N
  • Question Question
Advisor login, logout & break calculation
Replies
1
Views
255
Cubist
Cubist
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
400
freeb1893
F

Forum statistics

Threads
1,224,910
Messages
6,181,676
Members
453,061
Latest member
schiefA

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