+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Requesting a little help with active rate/ hour calculation

  1. #1
    Registered User
    Join Date
    01-06-2019
    Location
    Hungary
    MS-Off Ver
    2013
    Posts
    4

    Requesting a little help with active rate/ hour calculation

    Hey all,

    I'd like to ask for some help. I have a number or income value witch updates frequently lets say on A1, and i'd like to track how much is the rate / hour of it with a live tracking. It doesnt matter how many extra cells it take to for example make a live clock or anything else in addition, this is only for personal use. I have no idea how to start with, so far i achieved that if i input two times manually it calculates the difference in minutes, and thus i can tell whats the income / hour with this one. But i'd like to have an automaticated version where i set for example the starting time manually, and when i modify the money value it shows me the hourly income on another cell.

    For example starting time 16:00
    Value modified to 50
    Active clock 16:30
    Value / hour : 100

    And all i would have to change is the starting time and the value.
    Can anyone help me?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,287

    Re: Requesting a little help with active rate/ hour calculation

    Hi, welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    Pending a response to Ford's request let me take a leap in the dark seeing as how I had spent a few minutes:

    I think it has to be a bit of VBA if you want to automatically capture the time at which you added a new "Value"

    timestamp.png

    With reference to the above when you fill in a value in B6 the VBA will run and capture the current time in C6. Simple formulas in D6 and E6 will calculate the elapsed time and income per hour.

    Here's the VBA:

    Please Login or Register  to view this content.
    See also the attached workbook. Macros will need to be enabled for this to run.

    Is this something close to what you were looking for? If not please follow Ford's instructions carefully.
    Attached Files Attached Files
    Last edited by GeoffW283; 01-07-2019 at 02:01 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    ================================================
    THIS IS THE TEXT OF A PRIVATE MESSAGE FROM kawa212 TO ME
    SAYING THAT HE WAS UNABLE TO MAKE THIS POST HIMSELF
    =================================================

    It doesnt let me post reply to my thread somewhy. Here is the current one i wanted to send


    Thank you for the answer FDibbins and GeoffW283. I tried to start with Geoff's version. It is working nicely however when i wanted to import it to my sheet the current time stopped working properly. I copied the VBA code to my sheet too but i guess somehow i need to copy the macros to make it work. However i can't seem to find the macros, since its not under the macros tab, its empty on my sheet and on your sheet aswell. So currently i don't know how to process.

    The other thing is the format, i don't need neither the AM, neither the decimals nor the $. So my best guess is instead of AM format i just chose a different for example hh:mm in cell format. And i modify this row :
    _("$"* # ##0,00_);_("$"* (# ##0,00);_("$"* "-"??_);_(@_) i have to delete "$" -s and ,00 -s right?

    Thanks in advance.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,111

    Re: Requesting a little help with active rate/ hour calculation

    Did he say why? Thanks for letting us know. I shall investigate.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    Initial response to kawa212's message that I copy pasted to post #4:

    First, I don't know why you were unable to post a message yourself. Sometimes the security system will prevent posts with certain special characters, so I was suspicious of the custom format that you quoted at the end of your attempted post but it worked fine for me so I don't know why you had a problem.

    I will reply to your other issues in a short while.

    Geoff

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    Did he say why?
    Ali - no he didn't provide any additional info.
    Geoff

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,111

    Re: Requesting a little help with active rate/ hour calculation

    I've contacted him to see if we can get to the bottom of it. Thanks for your help!

  9. #9
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    Ali - thanks!

    kawa212:
    when i wanted to import it to my sheet the current time stopped working properly.
    I started off thinking I'll provide a step by step to copy the macro, but maybe the macro is working fine, but your worksheet layout is different. My macro as written is very simple. It looks for a change in some cell in column-B and then writes the current time into the cell that is the same row that the column-B change was detected in and one column to the right (column-C).
    Could this be the problem?

    Change the Range("b:b") to the column where you enter "value"
    Cells(1, 2) says write the time in the same row (1) as where the change was detected and one column to the right (2). Change these as you need.

  10. #10
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    The other thing is the format, i don't need neither the AM, neither the decimals nor the $. So my best guess is instead of AM format i just chose a different for example hh:mm in cell format. And i modify this row :
    _("$"* # ##0,00_);_("$"* (# ##0,00);_("$"* "-"??_);_(@_) i have to delete "$" -s and ,00 -s right?
    I'm sure you don't need a complex custom formula. For the times just choose a standard time format that you like. That's all there is to it. Again for the numeric columns just choose a currency or number format that you like - no need to get involved in custom formulas. Let us know if you need more detail.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,287

    Re: Requesting a little help with active rate/ hour calculation

    I got the PM as well, it seems OP tried to post a link, I suggested they just post without the link.

    If that still doesnt work, I will look into it deeper.

  12. #12
    Registered User
    Join Date
    01-06-2019
    Location
    Hungary
    MS-Off Ver
    2013
    Posts
    4

    Re: Requesting a little help with active rate/ hour calculation

    Thanks for the replies.

    I wanted to post a reply exactly what was the 4th comment, but it didn't let me somewhy.

    However thanks for the additional help, but im not really into it on this level, it sounds a little complicated for me to understand every row . Just trying to figure it out by simple logic. I took the solution what geoff made the timestamp xls and tried to import that system into mine. So i haven't found the macros under the developer tools-> macros, nor it worked when i copied from A1 to E20 (so basically everything which was on the sheet) and adding the Visual Basic --> sheet1 which included some code. And it just wasnt working, moreover if i just copy or cut the whole table from there and paste to column I to M its not working either so moving makes it ruin.
    This is beyond my excel knowledge so I just copied my formulas from my excel sheet to this one next to it :D which is not a solution, but its working and i don't really need it for long term.

  13. #13
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    If you would like to upload your workbook - the one where you are attempting to adapt my macro - then I should be able to tell you what needs to change.

    To attach a file click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

    And thanks for the reputation points!

  14. #14
    Registered User
    Join Date
    01-06-2019
    Location
    Hungary
    MS-Off Ver
    2013
    Posts
    4

    Re: Requesting a little help with active rate/ hour calculation

    There it is.

    Copied the sheet 1, then copied the whole cells of your timestamp, than saved is as a macro-friendly format.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    907

    Re: Requesting a little help with active rate/ hour calculation

    Thanks for uploading the example but I'm afraid I'm having a hard time seeing exactly what you want.

    You have pasted my original guess at what you wanted into cells M1:Q6. Did you simply want me to make the macro work exactly as it did with my initial example?

    If it's not that simple then I need to know how the macro should work with your data in columns D:I.

    From post #1:
    I'd like to have an automaticated version where i set for example the starting time manually, and when i modify the money value it shows me the hourly income on another cell.
    Also:
    For example starting time 16:00
    Value modified to 50
    Active clock 16:30
    Value / hour : 100
    And all i would have to change is the starting time and the value.
    In your spreadsheet:

    a) Which cell contains the "starting time"
    b) Which cell contains the "money value/value-modified"?
    c) Which cell will contain the automatically updated "active clock"?
    d) Which cell contains the "value / hour"?

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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