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.

SR #	Start Date Time	End Date Time
TSHJKL4356	6/1/2017 7:10	6/1/2017 11:03
TSHJKL4357	6/1/2017 13:15	6/1/2017 17:03
TSHJKL4358	6/1/2017 15:08	6/1/2017 20:22
TSHJKL4359	6/1/2017 16:08	6/1/2017 17:10
TSHJKL4360	6/1/2017 6:35	6/1/2017 9:24

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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

	A​	B​	C​
1​	SR #​	Start Date Time​	End Date Time​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​
3​	TSHJKL4357​	06/01/2017 13:15​	06/01/2017 17:03​
4​	TSHJKL4358​	06/01/2017 15:08​	06/01/2017 20:22​
5​	TSHJKL4359​	06/01/2017 16:08​	06/01/2017 17:10​
6​	TSHJKL4360​	06/01/2017 06:35​	06/01/2017 09:24​
7​	TSHJKL4360​	06/01/2017 18:00​	06/01/2017 20:00​

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