+ Reply to Thread
Results 1 to 18 of 18

time elapsed update

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    time elapsed update

    Elapsed Timer with Update button with a color-coding scheme wrt the values in the cells
    Attached Files Attached Files
    Last edited by snkhan; 06-24-2020 at 04:00 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: time elapsed update

    Excel will automatically adjust the formula for each row...

    Please Login or Register  to view this content.

    Alternatively...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 06-17-2020 at 08:26 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    Hello AlphaFrog,
    thank you for the reply. It works, well partially. I have created a button on the first worksheet and when this button is pressed the time elapsed (the code you provided) should run on the second sheet but it only runs on the first sheet instead. this is the code:

    for the button
    Please Login or Register  to view this content.
    and this is in the module:

    Please Login or Register  to view this content.
    please point out what am I doing wrong, thank u in advance

  4. #4
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    sorry forgot to attach the file
    Attached Files Attached Files

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: time elapsed update

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    thank you, it works but I don't want to show the formula in the cells, just the resultant value. how can I achieve this last part? thank u

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: time elapsed update

    Please Login or Register  to view this content.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: time elapsed update

    An alternative method is to use Power Query which is available in Excel 2010 as an Add - In. Here is the Mcode that is developed when using the U/I.



    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Carrier Time in Total Hours
    2
    6/11/2020 8:38
    255.5696881
    3
    6/10/2020 5:40
    282.5319104
    4
    6/10/2020 14:28
    273.7341326
    5
    6/10/2020 10:56
    277.2669104
    6
    6/10/2020 10:21
    277.8530215
    7
    6/11/2020 19:45
    244.4455215
    8
    6/11/2020 10:28
    253.7246881
    9
    6/11/2020 5:23
    258.8166326
    10
    6/11/2020 9:46
    254.4280215
    11
    6/11/2020 10:25
    253.7827437
    12
    6/11/2020 5:21
    258.8499659
    13
    6/11/2020 7:22
    256.836077
    14
    6/11/2020 7:34
    256.6321881
    15
    6/11/2020 9:47
    254.4196881
    16
    6/11/2020 13:23
    250.8077437
    17
    6/11/2020 13:55
    250.2863548
    18
    6/11/2020 15:30
    248.6969104
    19
    6/11/2020 16:50
    247.3696881
    20
    6/11/2020 17:00
    247.1963548
    21
    6/11/2020 17:36
    246.6005215
    22
    6/11/2020 17:15
    246.9505215
    23
    6/11/2020 17:40
    246.5363548
    Sheet: Sheet2
    Attached Files Attached Files
    Last edited by alansidman; 06-22-2020 at 02:14 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    thank you, works perfectly

  10. #10
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    it works perfectly but now I have another feature that I would like to add. I want to add a status column. I don't want the timer to update if the status column for that corresponding time/date is "Complete". please see attached file.
    or should I start a new thread for this feature?
    thank u in advance
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: time elapsed update

    Use this to update like before...

    Please Login or Register  to view this content.

    Use this to "lock" the time when you change the status to complete. It wont lock in time for rows that were changed to "Complete" previous to using this code.

    To install the code:
    • Right-click on the sheet tab.
    • Select View Code from the pop-up context menu.
    • Paste the code from below in the worksheet's code module.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    hello AlphaFrog,
    I have tried what you suggested and it is not working. After the complete status has been initialized and then the update is run, the 'completed' elapsed time still changes. please see attached file.
    Attached Files Attached Files

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: time elapsed update

    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: time elapsed update

    Quote Originally Posted by snkhan View Post
    hello AlphaFrog,
    I have tried what you suggested and it is not working. After the complete status has been initialized and then the update is run, the 'completed' elapsed time still changes. please see attached file.
    Your Update procedure is not the same as the one I suggested in post #11

    The data in your example workbooks keeps changing. In post #4, column B had formulas. In post 12, column B is all constants; no formulas. Which do you want; formulas or constant in column B ?

  15. #15
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    I don't want the formulas to show, just the resultant constants please and thnx

  16. #16
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: time elapsed update

    Quote Originally Posted by snkhan View Post
    I don't want the formulas to show, just the resultant constants please and thnx
    Use Bakerman2's code in post 13. You can delete the Worksheet_Change event procedure.

  17. #17
    Registered User
    Join Date
    03-26-2013
    Location
    Canada
    MS-Off Ver
    ms365
    Posts
    79

    Re: time elapsed update

    Thank you very much AlphaFrog and Bakerman2. I was able to get the results I wanted. I also incorporated a color scheme using the same code Bakerman2 provided. (wasn't able to get the value as a constant but as formula, but that's ok I guess for now), Im pleased. Thank you again. I have attached the final product.
    Attached Files Attached Files

  18. #18
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: time elapsed update

    You're welcome and thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 04-30-2018, 11:46 PM
  2. [SOLVED] How to subtract Time Values contained in Labels on a userform - Time Elapsed Live Clock
    By deadeye_draken in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-18-2017, 06:44 AM
  3. [SOLVED] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  4. [SOLVED] Creating a Time elapsed Spread Sheet that records and posts time...
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2013, 07:11 PM
  5. Replies: 2
    Last Post: 04-19-2012, 10:34 PM
  6. Using start/end time fields on userform to populate elapsed time on spreadsheet
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2011, 05:07 PM
  7. Replies: 2
    Last Post: 04-27-2011, 08:21 AM

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