I know there are various apps for recording time but prefer to process daily log files using Excel.
In Excel (2011) have times in format 00:11:14, hh:mm:ss. This is an amount of time.
These times represent duration of time a job that has taken to do, i.e. open/saved to closed.
I want to divide this time by 1 hour to get a fraction.
Then I can use that fraction to work out actual hourly rate we are earning for different clients.
Any suggestions as still in the valleys of the KM learning curve.
Converting a string to an integer value and back again to a string is easy enough. But it's hard to tell what you really want. If you are using Excel, you can easily create another column and each cell in that column could include a formula which converts the time in its neighbouring cell to the value that you want. I don't normally deflect a question about KM to another application, but it really seems that this is what you probably should do.
However it's certainly possible to solve in KM too. Are you try to convert an entire column, or a single cell? Are you trying to replace the contents of the cell, or put the new values into the clipboard or a variable? There are just so many ways to interpret the question, we should get clarification first.
I'm no longer in the valleys, but to be at the top of the hill one would need to understand all of KM's actions and features, and I'm sure not there yet.
Hi Sleepy - obviously got a lot of macros saving you time! Hope below helps clarify.
In screenshot column F is the calculated duration between opening & closing a file.
I want column H to show result of dividing the time values in column F by an hourly rate. The hourly rate can be stored as a constant in e.g. cell Z1.
Values in column H can be result of a simple function, e.g. =F3/$Z$1 - then just a matter of using Fill Down. Hourly rate in Z1 can be in number format as can column H.
I want to figure out how to divide a time formatted value by a number. For example, lets say our rate was $50 per hour (Z1=50) and a job takes 30 minutes. We already know by client name what the sale price will be - but not if a job/s is profitable at that rate.
We do know what our hourly rate needs to be stored in cell Z1 - (Annual Gross+Profit/Hours worked) simple but effective metric taken from Tari. If the rate for row 3, cell H3 is $45 then we are happy as we know we are above target rate. Below $25 then we are losing money on that specific job.
Assuming the "Duration" column F is a calculation of =B3-B2 which gives you the difference in units of days, then you can calculate the Hours easy enough: =F3 * 24
But your other request does not make any sense to me:
You are dividing Days by $/hr -- what do you expect that to give you?
Even if you convert days to hours, then you have hrs/($/hr) which is hrs squared / $ ???
To get hourly rate (Col H) you need this: $Amt_Charged_Client / Hours_Worked
I'll stop here, because one of us is confused, and no need to proceed until we are on the same page.
see Martins comments. Excel IS driving me bonkers.
You are correct, duration in cell F3 is =B3-B2. So that is the time the JOB in cell D3 has taken to do.
Column E contains the name of the client for whom the job in column D is being done.
We charge a different, but fixed rate per JOB for each client.
For each JOB I know a) the duration that the JOB took, column F, b) and I know how how much we charge per JOB to that client, so I should be able to calculate the prorata hourly rate. This would be easy if there was a common unit for time and money.
I have thought maybe, convert the value in F3 to seconds, express the hourly rate as $'s per second - then I could divide rate/time.
If we charge a client $75 per JOB, column D, then if that JOB takes 1 hour we know prorata rate is 75/1, $75 per hour. However, if the same job takes 2 hours then our hourly rate is reduced to 75/2, viz $37.5. We already know what our hourly rate across the board needs to be, regardless of individual client rates,say it is $45 per hour. Then we can see quite easily client profitability (or losses) by comparing individual JOB rates, and client summed and averaged rates against that single yardstick.
In theory easy but Excel is not easy when it comes to time formats.
Great. And you've just noticed one of my other secrets. I call myself Sleepy in order to tempt people to say that I'm "not so sleepy" and you fell for my trap.
Let's assume that the fixed fee you charged your client is in Col J.
So, the effective hourly rate ($/HR) you charged that client, say for Row 2 & 3 (RBKC), would be: = J2 / (F3 * 24)
