Error in Overlapping Time

[meowbhow]

Marcelo, please check the formula if it has some issue = SUM(IF(A$2:A$9999=A312,IF(1-((B312>C$2:C$9999)+(B$2:B$9999>C312)),IF(ROW(A$2:A$9999)>ROW(A312),IF(C$2:C$9999>C312,C312,C$2:C$9999)-IF(B$2:B$9999>B312,B$2:B$9999,B312)))))

Result should be 1 hour instead of 17:37 hours

Guzgi (USF) (Wateen OFS) Khuzdar	2017-12-04 22:19:28	12/04/2017 23:59:59	17:37:20
Guzgi (USF) (Wateen OFS) Khuzdar	2017-12-04 22:19:35	12/04/2017 23:59:59	15:56:49

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[meowbhow]

Marcelo please check why i am not getting accurate values.

 

[Marcelo Branco]

I don't understand your dates. The formula worked perfectly for me with proper dates as dd/mm/yyyy hh:mm:ss (April 12)

	A​	B​	C​	D​
1​	Name​	StartDate​	EndDate​	Result​
2​	Guzgi (USF) (Wateen OFS) Khuzdar​	12/04/2017 22:19:28​	12/04/2017 23:59:59​	01:40:24​
3​	Guzgi (USF) (Wateen OFS) Khuzdar​	12/04/2017 22:19:35​	12/04/2017 23:59:59​	00:00:00​

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

Adjust the dates and the formula should work.

M.

 

[meowbhow]

How about this data ? I have adjusted the date but this is the issue i am facing now.

Pull (USF) Khuzdar	2017-12-18 23:16:48	2017-12-19 00:29:48	2:35:56
Pull (USF) Khuzdar	2017-12-18 23:17:18	2017-12-18 23:37:26	0:20:08
Pull (USF) Khuzdar	2017-12-18 23:40:35	2017-12-19 00:01:26	0:41:42
Pull (USF) Khuzdar	2017-12-19 00:55:28	2017-12-19 08:00:23	6:43:25
Pull (USF) Khuzdar	2017-12-19 04:09:04	2017-12-19 07:54:55	0:00:00
Pull (USF) Khuzdar	2017-12-19 03:24:50	2017-12-19 04:08:20	0:00:00
Pull (USF) Khuzdar	2017-12-18 23:16:48	2017-12-19 00:29:48	0:41:57
Pull (USF) Khuzdar	2017-12-19 00:29:58	2017-12-19 00:54:33	0:21:04
Pull (USF) Khuzdar	2017-12-19 02:36:27	2017-12-19 02:59:35	0:00:00
Pull (USF) Khuzdar	2017-12-19 02:11:10	2017-12-19 02:34:15	0:00:00

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

 

[Marcelo Branco]

The formula works only if column B (Start Date) is in ascending order.

M.

 

[meowbhow]

Please provide a solution since my data is not always in ascending order. Thank you !!

 

[Marcelo Branco]

Please provide a solution since my data is not always in ascending order. Thank you !!

Sorry, i'm busy and it's easier to re-arrange your data. Try Data > Sort.

M.

 

[meowbhow]

All the data is in ascending order now, but still same problem.

Pull (USF) Khuzdar	2017-12-18 23:16:48	2017-12-19 00:29:48	2:35:56
Pull (USF) Khuzdar	2017-12-18 23:16:48	2017-12-19 00:29:48	1:22:56
Pull (USF) Khuzdar	2017-12-18 23:17:18	2017-12-18 23:37:26	0:00:00
Pull (USF) Khuzdar	2017-12-18 23:40:35	2017-12-19 00:01:26	0:20:51
Pull (USF) Khuzdar	2017-12-18 23:40:35	2017-12-19 00:01:26	0:00:00

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

 

[Marcelo Branco]

The formula worked perfectly for me

	A​	B​	C​	D​
1​	Name​	Start​	End​	Result​
2​	Pull (USF) Khuzdar​	2017-12-18 23:16:48​	2017-12-19 00:29:48​	02:14:50​
3​	Pull (USF) Khuzdar​	2017-12-18 23:16:48​	2017-12-19 00:29:48​	01:01:50​
4​	Pull (USF) Khuzdar​	2017-12-18 23:17:18​	2017-12-18 23:37:26​	00:00:00​
5​	Pull (USF) Khuzdar​	2017-12-18 23:40:35​	2017-12-19 00:01:26​	00:20:51​
6​	Pull (USF) Khuzdar​	2017-12-18 23:40:35​	2017-12-19 00:01:26​	00:00:00​

<tbody>
</tbody>

D2
=SUM(IF(A$2:A$11=A2,IF(ROW(A$2:A$11)>ROW(A2),IF(1-(B$2:B$11>C2)+(B2>C$2:C$11),IF(C2>C$2:C$11,C$2:C$11,C2)-IF(B2>B$2:B$11,B2,B$2:B$11)))))
Ctrl+Shift+Enter

Please, enter carefully the formula when doing tests. I've to leave now. Good luck.

M.

 

[meowbhow]

Sorry for bothering you, thank you for your help.