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?

================================================
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.

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

Did he say why?

Ali - no he didn't provide any additional info.
Geoff

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.

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.

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.

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!

There it is.

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

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"?

Hey Geoff

Yeah I just want it to work basically. But if you make it work within M1 to Q6 then if I kater choose to move it to for example U1 to Y6 will it work then?

for the questions
a, Any basically, it used to be B13, but it can be the M6 as in the table
b, It is E8 but it can be the N6 aswell
c, Can be the O6
d, Can be the Q6

It really doesnt matter how it lines up or where it is as long as it works, but back to the original question it would be nice to see how this is working if i might want to modify something later on.

OK - here is what you need to do in order to make my stuff work when it is located at M1 rather than A1:
1) Get into the VBA editor by some means. For example right click on the worksheet name and select "View code"
2) Change Range("b:b") to Range("n:n")
That's it!

The VBA should now look like:

Please Login or Register  to view this content.

Test it by entering a "Start time" in column M and a "Value" in column N and you should see a timestamp automatically written to column-O.

If you want to locate my stuff somewhere else then simply make the appropriate change to the same line of VBA as noted in step-2 above.

Let me know how you get on.