# Thread: Calculation of time for several incidents.

1. ## Calculation of time for several incidents.

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.

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.

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

3. Hi Philips,

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•