Results 1 to 3 of 3

Thread: Calculation of time for several incidents.

  1. #1

    Calculation of time for several incidents.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #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. #3
    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

Posting Permissions

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