Calculation of time for several incidents.

[kapilkumar]

HI,

GOOD AFTERNOON EVERY ONE.

Hi, I have several incidents for 1 ATM ID with "Open Time" and "Close Time", i need to calculate the "Close Time" MINUS "Open Time"

and would need over lapping Incidents to be excluded while calculating.

Please find the sample below.
the last 3 Incidents are overlapping, eg ( 4,5,6). the calculated time should give zero.

ATM ID	INCIDENT	Open Time	Close Time
DDEL1780	1	2/26/2014 0:00	2/26/2014 9:02
DDEL1780	2	2/26/2014 13:41	2/26/2014 18:30
DDEL1780	3	2/26/2014 19:02	2/26/2014 23:59
DDEL1780	4	2/26/2014 19:11	2/26/2014 20:13
DDEL1780	5	2/26/2014 20:30	2/26/2014 23:59
DDEL1780	6	2/26/2014 20:30	2/26/2014 23:59

Thanks and Regards

Kapilkumar.

[Bob Phillips]

Try

=IF(SUMPRODUCT(((C2>=$C$2:$C$10)*(C2<=$D$2:$D$10))+((D2>=$C$2:$C$10)*(D2<=$D$2:$D$10)))=2,D2-C2,0)

[kapilkumar]

Hi Philips,

Many thanks for your response.

One more think i missed to mention, that if the 2nd Incident closes after the 1st incidents but opens before the 1st incident closes then the 2nd incident start time should considered as close time of 1st incident or wise versa.

Please find the manually calculated incidents in "Calculation" column for your reference.

ATM ID	INCIDENT	Open Time	Close Time	Calculation
DDEL1780	1	2/26/2014 0:00	2/26/2014 9:02	9:02
DDEL1780	2	2/26/2014 13:41	2/26/2014 18:30	4:49
DDEL1780	3	2/26/2014 19:02	2/26/2014 23:59	4:57
DDEL1780	4	2/26/2014 19:11	2/26/2014 20:13	0:00
DDEL1780	5	2/26/2014 20:30	2/26/2014 23:59	0:00
DDEL1780	6	2/26/2014 20:30	2/26/2014 23:59	0:00

Regards
Kapilkumar