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.

 

[Gerald Higgins]

Please explain exactly how you arrived at the answer of 11:42 hours.

I think I can see how, but I don't want to guess wrong.

This might be achievable, if you can resort your data, and use helper columns.
Are these options for you ?

 

[stunnrock]

Did you mean 11:35, rather than 11:42? If so, this will do it:

=SMALL($C$2:$C$6,1)-SMALL($B$2:$B$6,1)+SUMPRODUCT(SMALL($C$2:$C$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))-((SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))>SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1))))*SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1))))-((SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))<=SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1))))*SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1)))))

 

[barry houdini]

Hey stunrock,

Nice formula! Any chance of explaining how that works?

 

[stunnrock]

Okay, first think of the two time columns sorted (seperately).

The calc starts with subtracting the earliest starting time from the earliest finishing time. After that it's a case of working through the lists with the following logic: If starting time 2 is greater than finishing time 1, then add the time between starting time 2 and finishing time 2, otherwise add the time between finishing time 1 and finishing time 2.

I hope that makes sense.

By the way, this is a very slight improvement:

=MIN($C$2:$C$6)-MIN($B$2:$B$6)+SUMPRODUCT(SMALL($C$2:$C$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))-((SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))>SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1))))*SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1))))-((SMALL($B$2:$B$6,ROW(INDIRECT("2:"&COUNT($C$2:$C$6),1)))<=SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1))))*SMALL($C$2:$C$6,ROW(INDIRECT("1:"&COUNT($C$2:$C$6)-1,1)))))

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	SR #​	Start Date Time​	End Date Time​		Minutes​	Minutes​		Result (Minutes)​	Hours​	Minutes​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​		430​	663​		695​	11​	35​
3​	TSHJKL4357​	06/01/2017 13:15​	06/01/2017 17:03​		795​	1023​
4​	TSHJKL4358​	06/01/2017 15:08​	06/01/2017 20:22​		908​	1222​
5​	TSHJKL4359​	06/01/2017 16:08​	06/01/2017 19:10​		968​	1150​
6​	TSHJKL4360​	06/01/2017 06:35​	06/01/2017 09:24​		395​	564​
7​

Formula in E2 copied across to F2 and down
=60*HOUR(B2)+MINUTE(B2)

Formula in H2
=SUMPRODUCT(--(COUNTIFS(E2:E6,"<="&ROW(INDIRECT(MIN(E2:F6)&":"&MAX(E2:F6))),F2:F6,">"&ROW(INDIRECT(MIN(E2:F6)&":"&MAX(E2:F6))))>0))

Formula in I2
=INT(H2/60)

Formula in J2
=MOD(H2,60)

Hope this helps

M.

 

[barry houdini]

Hello Marcelo,

That works for me - I looked at doing this a similar way, a method that examines every minute to ascertain whether it falls in any of the date ranges.

This version does that too, but doesn't require any helper cells and will work over multiple days

=SUMPRODUCT(0+(COUNTIFS(B2:B6,"<"&ROW(INDIRECT("1:"&ROUND(1440*(MAX(C2:C6)-MIN(B2:B6)),0)))/1440+MIN(B2:B6)-1/2880,C2:C6,">"&ROW(INDIRECT("1:"&ROUND(1440*(MAX(C2:C6)-MIN(B2:B6)),0)))/1440+MIN(B2:B6)-1/2880)>0))/1440

It has several disadvantages

1) It's limited by the number of rows in the worksheet, so on current worksheet length that limits the data period to (2^20)/1440 days which is approximately 2 years
2) If you use a period that long, or close to it, then the formula is very slow
3) Like your version it only handles whole minutes

For those reasons I recommend stunnrock's solution which works very well and doesn't have those disadvantages (although I'd love to make it shorter )

 

[Marcelo Branco]

Hi Barry,
I did not look with due care the formula suggested by stunrock, because I found it a bit confusing, but I'm going to look at it more closely and try to understand it.

I tried several different formulas and I was very close to getting something that worked - maybe you can help me to find out a simpler formula.

For example, this worked for me

	A​	B​	C​	D​	E​	F​	G​	H​
1​	SR #​	Start Date Time​	End Date Time​		Hours​	Minutes​		Helper​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​		11​	35​		0,093056​
3​	TSHJKL4357​	06/01/2017 13:15​	06/01/2017 17:03​					0,079861​
4​	TSHJKL4358​	06/01/2017 15:08​	06/01/2017 20:22​					0,126389​
5​	TSHJKL4359​	06/01/2017 16:08​	06/01/2017 19:10​					0​
6​	TSHJKL4360​	06/01/2017 06:35​	06/01/2017 09:24​					0​

<tbody>
</tbody>

Helper
Array formula in H2
=IFERROR(INDEX(IF(ROW(A$2:A$6)>ROW(A2),IF(1-((B$2:B$6>C2)+(B2>C$2:C$6)),IF(C2>C$2:C$6,C$2:C$6,C2)-IF(B2>B$2:B$6,B2,B$2:B$6))),MATCH(TRUE,ISNUMBER(IF(ROW(A$2:A$6)>ROW(A2),IF(1-((B$2:B$6>C2)+(B2>C$2:C$6)),IF(C2>C$2:C$6,C$2:C$6,C2)-IF(B2>B$2:B$6,B2,B$2:B$6)))),0)),0)

Formula in E2
=HOUR(SUMPRODUCT(C2:C6-B2:B6)-SUM(H2:H6))

Formula in F2
=MINUTE(SUMPRODUCT(C2:C6-B2:B6)-SUM(H2:H6))

--> The idea is to get (discount) only the first overlap

But failed miserably in this scenario

	A​	B​	C​	D​	E​	F​	G​	H​
1​	SR #​	Start Date Time​	End Date Time​		Hours​	Minutes​		Helper​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​		13​	35​		0,093056​
3​	TSHJKL4357​	06/01/2017 13:15​	06/01/2017 17:03​					0,079861​
4​	TSHJKL4358​	06/01/2017 15:08​	06/01/2017 20:22​					0,043056​
5​	TSHJKL4359​	06/01/2017 16:08​	06/01/2017 17:10​					0​
6​	TSHJKL4360​	06/01/2017 06:35​	06/01/2017 09:24​					0​
7​	TSHJKL4360​	06/01/2017 18:00​	06/01/2017 20:00​					0​

<tbody>
</tbody>

M.

 

[Marcelo Branco]

Hi Barry

I *think* i created a simpler formula. See, please, if it's ok

Scenario 1 (just one day)

	A​	B​	C​	D​	E​
1​	SR #​	Start Date Time​	End Date Time​		Result​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​		11:35:00​
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​

Array formula in E2
=MIN(C2:C7)-MIN(B2:B7)+SUM(SMALL(C2:C7,ROW(C3:C7)-ROW(C2)+1)-IF(SMALL(C2:C7,ROW(C2:C6)-ROW(C2)+1)>SMALL(B2:B7,ROW(B3:B7)-ROW(B2)+1),SMALL(C2:C7,ROW(C2:C6)-ROW(C2)+1),SMALL(B2:B7,ROW(B3:B7)-ROW(B2)+1)))
Ctrl+Shift+Enter

Scenario 2 (two days with the same times)

	A​	B​	C​	D​	E​
1​	SR #​	Start Date Time​	End Date Time​		Result​
2​	TSHJKL4356​	06/01/2017 07:10​	06/01/2017 11:03​		23:10:00​
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​
8​	TSHJKL4356​	07/01/2017 07:10​	07/01/2017 11:03​
9​	TSHJKL4357​	07/01/2017 13:15​	07/01/2017 17:03​
10​	TSHJKL4358​	07/01/2017 15:08​	07/01/2017 20:22​
11​	TSHJKL4359​	07/01/2017 16:08​	07/01/2017 17:10​
12​	TSHJKL4360​	07/01/2017 06:35​	07/01/2017 09:24​
13​	TSHJKL4360​	07/01/2017 18:00​	07/01/2017 20:00​

Array Formula in E2
=MIN(C2:C13)-MIN(B2:B13)+SUM(SMALL(C2:C13,ROW(C3:C13)-ROW(C2)+1)-IF(SMALL(C2:C13,ROW(C2:C12)-ROW(C2)+1)>SMALL(B2:B13,ROW(B3:B13)-ROW(B2)+1),SMALL(C2:C13,ROW(C2:C12)-ROW(C2)+1),SMALL(B2:B13,ROW(B3:B13)-ROW(B2)+1)))
Ctrl+Shift+Enter

E2 format --> [h]:mm:ss

Hope i did not miss something ray:


M.

 

[Marcelo Branco]

Looking at the stunrock formula now, i see that the logic of my formula is identical (or very similar) to that of his formula.

M.