+ Reply to Thread
Results 1 to 9 of 9

Downtime Analysis

  1. #1
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Downtime Analysis

    Hi everyone, I am looking forward to solve this problem:
    Example:
    Downtime Explanation.jpg
    Better quality: http://postimage.org/image/krj3hfqgn/full/

    COL 1 is the Input data from Input worksheet, now..if in COL 1 between the times is 3 or more minutes gap, it is known as downtime. I want excel to recognize downtime after 3 or more minutes gap between two different times in (COL 1), and export or (mirror) those two different times in to COL 2, so that i can see without me lookin into COL 1.
    Than show the acctual gap time for each downtime in the COL 3 as shown in the example on the left.

    The whole point is,COL 1 is going to be an input, and the rest is going to be automatic, without me looking into the COL 1 myselfe, and picking out two different times with 3 or more minutes gap, and importing them into COL 2.

    What formulas,functions i have to create, examples would be nice Thank you!
    Last edited by Edd Win; 03-11-2013 at 07:31 PM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Downtime Analysis

    can you post a sample spreadsheet - are you only comparing the cell below each time

    in which case you could use in cell F6
    =IF(D7>D6+TIME(0,3,0),D6,"")
    In H6
    =IF(F6<>"",D7-D6,"")
    in J6
    then you can sum column H

    see attached
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Downtime Analysis

    Thank you!! thats the way i wanted it to work
    But i forgot to state that..since the downtime starts after 3 minutes, how can i set limitation like: downtime starts when Greater than 3 minutes, but less than 5 hours?

    Downtime Analysis.xlsx

    P.S. You guys are awesome in this forum!, i just reacently started to work with excel, and i am totaly raw at this :|
    Thank you once again!

  4. #4
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Downtime Analysis

    Thank you!! thats the way i wanted it to work
    But i forgot to state that..since the downtime starts after 3 minutes, how can i set limitation like: downtime starts when Greater than 3 minutes, but less than 5 hours?

    Attachment 220266

    P.S. You guys are awesome in this forum!, i just reacently started to work with excel, and i am totaly raw at this :|
    Thank you once again!

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Downtime Analysis

    does this work for you
    =IF(AND(D7>D6+TIME(0,3,0),D7<D6+TIME(5,0,0)),D6,"")
    so 3 mins is not included as is 5hrs is not included
    otherwise you need =
    =IF(AND(D7>=D6+TIME(0,3,0),D7<=D6+TIME(5,0,0)),D6,"")

  6. #6
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Downtime Analysis

    Thank you "etaf", i will try this tomorrow, and will let you know.

  7. #7
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Downtime Analysis

    Worked like a charm! Thank you for the help

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Downtime Analysis

    your welcome :up: thanks for letting us know

    to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

  9. #9
    Registered User
    Join Date
    03-10-2013
    Location
    dungannon
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Downtime Analysis

    Ok will do, thanks once again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1