Calculation of time for several incidents.

kapilkumar

New member
Joined
Feb 28, 2014
Messages
2
Reaction score
0
Points
0
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 IDINCIDENTOpen Time Close Time
DDEL1780
12/26/2014 0:002/26/2014 9:02
DDEL178022/26/2014 13:412/26/2014 18:30
DDEL178032/26/2014 19:022/26/2014 23:59
DDEL178042/26/2014 19:112/26/2014 20:13
DDEL178052/26/2014 20:302/26/2014 23:59
DDEL178062/26/2014 20:302/26/2014 23:59

Thanks and Regards

Kapilkumar.
 
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)
 
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 IDINCIDENTOpen TimeClose TimeCalculation
DDEL178012/26/2014 0:002/26/2014 9:029:02
DDEL178022/26/2014 13:412/26/2014 18:304:49
DDEL178032/26/2014 19:022/26/2014 23:594:57
DDEL178042/26/2014 19:112/26/2014 20:130:00
DDEL178052/26/2014 20:302/26/2014 23:590:00
DDEL178062/26/2014 20:302/26/2014 23:590:00

Regards
Kapilkumar
 
Back
Top