Elapsed Time calculation

[itzashish]

Hi There,

Any quick fix, please?
I need the total open time of the SRs excluding any overlapping hours. In the below example, the total time is 11:42 hrs. Bt in the actual data dump, there would be even more number of rows for the same SR with no sorting.

[TABLE="width: 600"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]SR #[/TD]
[TD]Start Date Time[/TD]
[TD]End Date Time[/TD]
[/TR]
[TR]
[TD]TSHJKL4356[/TD]
[TD="align: right"]6/1/2017 7:10[/TD]
[TD="align: right"]6/1/2017 11:03[/TD]
[/TR]
[TR]
[TD]TSHJKL4357[/TD]
[TD="align: right"]6/1/2017 13:15[/TD]
[TD="align: right"]6/1/2017 17:03[/TD]
[/TR]
[TR]
[TD]TSHJKL4358[/TD]
[TD="align: right"]6/1/2017 15:08[/TD]
[TD="align: right"]6/1/2017 20:22[/TD]
[/TR]
[TR]
[TD]TSHJKL4359[/TD]
[TD="align: right"]6/1/2017 16:08[/TD]
[TD="align: right"]6/1/2017 17:10[/TD]
[/TR]
[TR]
[TD]TSHJKL4360[/TD]
[TD="align: right"]6/1/2017 6:35[/TD]
[TD="align: right"]6/1/2017 9:24[/TD]
[/TR]
</tbody>[/TABLE]


Please help with a formula or code.

 

[Tetra201]

Here is my take on it. This array formula returns 11:35 for the dataset from Post # 1:

=MAX(C2:C6)-MIN(B2:B6)-SUM(IF(B3:B6-C2:C5>0,B3:B6-C2:C5))

 

[Marcelo Branco]

Here is my take on it. This array formula returns 11:35 for the dataset from Post # 1:

=MAX(C2:C6)-MIN(B2:B6)-SUM(IF(B3:B6-C2:C5>0,B3:B6-C2:C5))

Yes it works with dataset from post 1, but doesn't seem to work with

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

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

B​

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

C​

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

1​

[/td][td]

SR #​

[/td][td]

Start Date Time​

[/td][td]

End Date Time​

[/td][/tr]

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

2​

[/td][td]

TSHJKL4356​

[/td][td]

06/01/2017 07:10​

[/td][td]

06/01/2017 11:03​

[/td][/tr]

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

3​

[/td][td]

TSHJKL4357​

[/td][td]

06/01/2017 13:15​

[/td][td]

06/01/2017 17:03​

[/td][/tr]

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

4​

[/td][td]

TSHJKL4358​

[/td][td]

06/01/2017 15:08​

[/td][td]

06/01/2017 20:22​

[/td][/tr]

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

5​

[/td][td]

TSHJKL4359​

[/td][td]

06/01/2017 16:08​

[/td][td]

06/01/2017 17:10​

[/td][/tr]

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

6​

[/td][td]

TSHJKL4360​

[/td][td]

06/01/2017 06:35​

[/td][td]

06/01/2017 09:24​

[/td][/tr]

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

7​

[/td][td]

TSHJKL4360​

[/td][td]

06/01/2017 18:00​

[/td][td]

06/01/2017 20:00​

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

M.

 

[Tetra201]

Ah, the column sorting... Let's see if this one works better (also an array formula):

=MAX(C2:C7)-MIN(B2:B7)-SUM(IF(SMALL(B2:B7,ROW(INDIRECT("2:"&COUNT(B2:B7))))-SMALL(C2:C7,ROW(INDIRECT("1:"&COUNT(C2:C7)-1)))>0,SMALL(B2:B7,ROW(INDIRECT("2:"&COUNT(B2:B7))))-SMALL(C2:C7,ROW(INDIRECT("1:"&COUNT(C2:C7)-1)))))