+ Reply to Thread
Results 1 to 9 of 9

Downtime Analysis

  1. 03-11-2013, 07:11 PM #1
    Edd Win
    Edd Win is offline
    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.
    Reply With QuoteRegister To Reply
  2. 03-12-2013, 07:19 AM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,037

    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
    Reply With QuoteRegister To Reply
  3. 03-12-2013, 01:48 PM #3
    Edd Win
    Edd Win is offline
    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!
    Reply With QuoteRegister To Reply
  4. 03-12-2013, 01:48 PM #4
    Edd Win
    Edd Win is offline
    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!
    Reply With QuoteRegister To Reply
  5. 03-12-2013, 01:54 PM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,037

    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,"")
    Reply With QuoteRegister To Reply
  6. 03-12-2013, 06:10 PM #6
    Edd Win
    Edd Win is offline
    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.
    Reply With QuoteRegister To Reply
  7. 03-13-2013, 06:20 PM #7
    Edd Win
    Edd Win is offline
    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
    Reply With QuoteRegister To Reply
  8. 03-13-2013, 06:22 PM #8
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,037

    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.
    Reply With QuoteRegister To Reply
  9. 03-15-2013, 02:59 PM #9
    Edd Win
    Edd Win is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1