+ Reply to Thread
Results 1 to 14 of 14

calculating multiple stop/start timers within a cell

  1. 12-07-2010, 10:35 PM #1
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    calculating multiple stop/start timers within a cell

    hi guys,

    in the attached sample file, i'm trying to figure out the "time to finish" for each item number. i have a start timestamp and an end timestamp. the result is a number of days to "finish".

    easy enough until i factor in that each item number can be suspended. i want to remove that time the item was suspended from my "time to finish" result.

    i have one column for the suspend start timestamp, and another column for the suspend stop timestamp. each is enclosed in a set of {}. it gets difficult because each cell here can have multiple timestamps within the {}, each separated by a comma (as seen in the sample file). so for an item suspended 3 separate time, a cell in column E would have 3 suspend start timestamps, and the corresponding suspend stop timestamps are in the next cell in column F.

    is there an easy way to do calculate the "time in suspend" in column G so I can subtract that from the time to finish in column D?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-07-2010, 11:30 PM #2
    Maistrye
    Maistrye is offline
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: calculating multiple stop/start timers within a cell

    Hi,

    Here's a crazy formula that should do what you ask. (It was entered with CTRL+SHIFT+ENTER)

    I'd actually recommend that you use a User-Defined Function instead. What I gave you will be extremely difficult to maintain, whereas a tidy UDF would make everyone happy.

    If you'd like one, just say so and one of us can paste something that will do this.

    S
    Attached Files Attached Files
    ------------------------------------------------------------------------------------------
    If you need no more help on the current problem, please mark it as "Solved". It saves time
    as many of us will look at threads if they are not marked as "Solved".

    The instructions on how to do this are found in the Forum Rules thread that is at top of every forum.
    (Currently you'll have to look at point #9.)
    ------------------------------------------------------------------------------------------
    Reply With QuoteRegister To Reply
  3. 12-08-2010, 12:00 AM #3
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    thanks a lot...it seems like i could just copy+paste the formula right down the page? what did you mean by "difficult to maintain"?
    Reply With QuoteRegister To Reply
  4. 12-08-2010, 12:10 AM #4
    Maistrye
    Maistrye is offline
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581

    Re: calculating multiple stop/start timers within a cell

    If you ever need to adjust the formula because your inputs have changed, it will be difficult to do. (It's sort of long and it's not really obvious what is actually being done in it)

    If you set it up as a UDF, you could pretty much call it as:

    =CalculateTimeDifference(Start,End,SuspendStart,SuspendEnd)
    or
    =CalculateTimeDifference(E2,E3,E4,E5)

    The function itself could be set up in a much more intelligible fashion. That's all I meant.

    S
    Reply With QuoteRegister To Reply
  5. 01-10-2011, 11:48 AM #5
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    Hi,

    Having some trouble with this. It appeared to work at first, but now I see that it's not calculating some lines that have multiple stop/start timers. in the attached sheet, rows 6-8 have 3 sets of stop/start timers...so the time to complete should be much less than the 10+ days that the formula is returning.

    am I missing something? I just copied the formula from cell D2 all the way down.
    Attached Files Attached Files
    Last edited by kmfdm515; 01-10-2011 at 11:54 AM.
    Reply With QuoteRegister To Reply
  6. 01-21-2011, 10:52 AM #6
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    anyone have any ideas?
    Reply With QuoteRegister To Reply
  7. 01-24-2011, 10:10 AM #7
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    trying once more...
    Reply With QuoteRegister To Reply
  8. 01-24-2011, 10:35 AM #8
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculating multiple stop/start timers within a cell

    Not entirely sure I follow but assuming a US regional config (unspecified)

    Please Login or Register  to view this content.
    Above would not work pre XL2007 given nesting levels

    Needless to say a UDF would be far more practical and would be pretty straightforward to code.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  9. 01-24-2011, 10:55 AM #9
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculating multiple stop/start timers within a cell

    UDF wise - based on sample:

    Please Login or Register  to view this content.
    called from cell:

    Please Login or Register  to view this content.
    though I'm sure with though the UDF could be simplified further.

    to confirm the UDF and prior formula return identical results:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 01-24-2011, 11:29 AM #10
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    thanks a lot, i'll give this a try.
    Reply With QuoteRegister To Reply
  11. 01-24-2011, 11:36 AM #11
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    hmmm...i'm getting a NAME error, not sure what i did wrong here.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 01-24-2011, 11:40 AM #12
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: calculating multiple stop/start timers within a cell

    UDFs must stored in Standard Modules (not Sheet Objects [nor ThisWorkbook])
    Reply With QuoteRegister To Reply
  13. 01-24-2011, 11:52 AM #13
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: calculating multiple stop/start timers within a cell

    e.g.
    Please Login or Register  to view this content.

    Reply With QuoteRegister To Reply
  14. 01-24-2011, 02:20 PM #14
    kmfdm515
    kmfdm515 is offline
    Forum Contributor
    Join Date
    08-18-2004
    Posts
    108

    Re: calculating multiple stop/start timers within a cell

    perfect, thanks. added a UDF (for the first time).

    new calculation is correct (compared to old formula)

    0.00 0.00
    0.01 0.01
    0.01 0.01
    0.01 0.01
    0.43 10.83
    0.45 10.84
    0.46 10.84

    Thanks for the help, everyone.
    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)

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