Over lapping time calculation for same entries

[meowbhow]

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]

 

[meowbhow]

OMG, You are not HUMAN. You are amazing. I owe you my life. You are great, brother.

 

[Marcelo Branco]

Thank you for the kind words. Glad to help

M.

 

[meowbhow]

Brother, instead of row 17. If my last row is variable how can i do this ? for example i want to use it for different reports, row 190 or row 220

 

[meowbhow]

For large set of data the value is coming wrong. Pls check how can i do the same for atleast 9999 rows.

=SUM(IF(A$2:A$400=A9,IF(1-((B9>C$2:C$400)+(B$2:B$400>C9)),IF(ROW(A$2:A$400)>ROW(A9),IF(C$2:C$400>C9,C9,C$2:C$400)-IF(B$2:B$400>B9,B$2:B$400,B9)))))

[TABLE="width: 637"]
<colgroup><col><col span="2"><col></colgroup><tbody>[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="align: right"]-86141.84903[/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="align: right"]-86141.87108[/TD]
[/TR]
[TR]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: right"]2017-11-08 04:49:54[/TD]
[TD="align: right"]2017-11-08 05:21:25[/TD]
[TD="align: right"]-86141.89296[/TD]
[/TR]
[TR]
[TD]Torani (USF) Khuzdar[/TD]
[TD="align: right"]2017-11-08 04:49:57[/TD]
[TD="align: right"]2017-11-08 05:21:14[/TD]
[TD="align: right"]-86141.91469
[/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="align: right"]0.005081019[/TD]
[/TR]
</tbody>[/TABLE]

 

[meowbhow]

Oh OKAY !! I guess its because some of data has empty values, kindly modify the formula to skip the empty formula.

 

[Marcelo Branco]

I don't understand your problem


[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"]

9​

[/td][td]

Torani (USF) Khuzdar​

[/td][td]

08/11/2017 04:49:48​

[/td][td]

08/11/2017 05:21:47​

[/td][td]

01:34:33​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Torani (USF) Khuzdar​

[/td][td]

08/11/2017 04:49:51​

[/td][td]

08/11/2017 05:21:36​

[/td][td]

01:02:48​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Torani (USF) Khuzdar​

[/td][td]

08/11/2017 04:49:54​

[/td][td]

08/11/2017 05:21:25​

[/td][td]

00:31:17​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Torani (USF) Khuzdar​

[/td][td]

08/11/2017 04:49:57​

[/td][td]

08/11/2017 05:21:14​

[/td][td]

00:00:00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/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:00:00​

[/td][/tr]
[/table]

Formula in D9 copied down (your formula above)
=SUM(IF(A$2:A$400=A9,IF(1-((B9>C$2:C$400)+(B$2:B$400>C9)),IF(ROW(A$2:A$400)>ROW(A9),IF(C$2:C$400>C9,C9,C$2:C$400)-IF(B$2:B$400>B9,B$2:B$400,B9)))))
Ctrl+Shift+Enter

Format column D as Time (hh:mm:ss)

M.