Multiply Hours By Hourly Rate
Similar Excel Video Tutorials
Gross Pay and Overtime: 5 Examples
- Learn about:
1.Calculating Gross pay using the ROUND function
2.Overtime for over 40 hours worked in a week
3.Overtime for over 40 hour ...
I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Dang... the hardest part of trying to ask for help is trying to figure out how to say what you want to say!!!
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80
Jan. 2 joe gets $1 raise
Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
Does this make sense???
Thanks for your help. If confused let me know and i will
When I subtract 15:30 from 09:00, I get 6:30. But when i use this as hours worked and multiply it by an hourly rate, i get an inaccurate number. How do I accomplish this. I just want to be able to subtract an ending time from the beginning time and multiply it by an hourly rate.
So, i have a table which is co-ordinating hours worked for my clients.
However, this has been generated into a box where i am using a hh:mm format.
I am then inputing an hourly rate and require a multiplication to take
place. As i have a different value for different clients, i need to be able
to input a number and this to be multiplied by the hours.
I am going crazy as the Excel help facility is either sending me round in
circles or giving me help which is no help at all.
I am having trouble with an if statement that calculates pay rates. The
rules are the first 20 hours are straight time, the next 10 (Between 20 and
30 hours) are at 1.5 times the hourly rate, then anything over 30 hours is 2
times the hourly rate. I am obvoiusly multiplying all of my hours here by
it's hourly rate times 2 - I just can't seem to fit it all in without adding
Start time Stop time Hrly rate Hours worked Gross pay Correct Answer
7:00 22:57 10.75 39:57 $859.14 $590.39
Sorry for this lame post.....
I've a cell showing the amount of hours spent on a project (68:45:00).
My hourly rate is $ 105
How do I convert the hours into a number which I can then multiply with 105
to get the correct cost?
I've been working on this equation for a couple of hours now and just know that I'm missing something simple. Any help would be greatly appreciated.
Below is a section of the spreadsheet that I have created to calculate payouts under specific conditions. The payouts are all based on a weekly rate. However, all of our data is either an annual rate or an hourly rate and the user would be entering it in that way. The user would be entering either an annual or an hourly rate as appropriate under the applicable column, leaving the other column blank. I want to be able to put a single formula under the weekly rate that will calculate the weekly rate as appropriate and as per how the rate was entered.
For example - to calculate the weekly rate using an annual salary the formula is - $annual rate/52.18. Using an hourly rate the formula is $hourly rate * hours of work per week.
How can I incorporate both of these formulas into one, so that the user can enter the rate field as either annual or hourly and the formula will calculate the weekly rate.
Hours of Work
Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
I have 5 cells in a row with a different hourly rate across the top of the page.
I have 5 cells in a row below the hourly rate row with labor hours.
I want to total the labor hours for that row times the hourly rate and have it total like this.
Row 2 is the hourly rate.
Row 4 is the hours.
My question is when I paste this formula down the columns the row 2 increments.
I have a lot of rows and don't want to edit each formula.
Is there a way around this?
I hope there is an easier way to calculate the following in excel:
I am endeavoring to determine the weighted hourly rate of pay to determine the 75th percentile for a market wage rate study.
Example of data
3 hours at $45, 160 hours at $25, 900 hours at $65. (this is 3 examples of hour associated with rates that need to be included in the formula.) I need to count each rate individually times the hours that are available at that rate.
My hope is that there is a way to do this without having to manually list the $45 rate 3 x's in a column, $25 rate 160 x's in same column and $65 900 times in same column to then be able to formulate the statistic percentage in column count at 0.75.
Bottomeline issue is need hours in one column where each different rate is accounted for times the number of hours that need to be weighted for the rate.
Thanks for any insight for better way to do this!!!!
This is my second post and I thought I would ask a excel question.
I am making a sheet to keep track of man hours worked during the week.
I can format the cell to figure out hours worked for a day by Subtracting the end time from the start time +12.
Start 8, End 4, Total Hours worked 8 hours or = C7-B7+12 is 8 hours.
if I SUM the 5 cells I use for the amount of the five work days totals hours, AND it's over 40!! How do I
Get 1 through 40 hours to *hourly rate, and any overtime hours to *by 1.5 the hourly rate? (DISCLAIMER)I am very much a beginner so please go easy if it is at all possible.
THANKS for your help or even to be pointed in the right direction.
I am trying to set myself up a timesheet using Office 2007 (OS - Vista Home Premium 32 bit)
I enter my start time and finish time and ask for the time difference - I then have a hidden column which converts the time difference to a general number so I can use it to multiply with my hourly rate. My problem is that the hidden column does not return the converted minutes, just the hours so therefore when I multiply it with my hourly rate is not correct as it misses the minutes.
This is how it looks in row 3
In A3 I enter the date e.g. 10/03/08
In B3 I enter my start time e.g. 09:30
In C3 I enter my finish time e.g. 17:00
In D3 I have =IF(A3="","",C3-B3) which returns 08:30
In E3 I have =IF(D3="","",HOUR(D3)) which returns 8 (this is a hidden column)
This is where the problem lies as I need it to convert 08:30 to 8.5 so i can multiply it with my hourly rate.
In F3 I have =IF(E3="","",IF(E3>=7,$F$1,IF(E3=4,$F$1/2,$F$1/7.5*E3))) which returns what I should charge but if I work anything with a 1/2 hour in it the results do not calculate it.
I'm sure this is something very simple and would appreciate any help with which function I should use in E3 to return the 8.5 needed.
Can someone help with a small problem that I'm having. I'm trying to format
a group of cells so I can calculate hours of work , then multiply that by my
hourly wage. I'd like it to read so that I can add a list of hours and then
multiply it by the hour rate of pay. (I.E. 8.30 hr + 8.30 hr + 8.30 hr =
25.30 X $10.00 = $253.00 )
I am starting with a very simple spreadsheet just to figure out the mechanics.
As you can see, in B2 and B3 I have 2 time objects.
For normal Hours I have =D34+(B2>D34)-B2
For After Hours I have =B3+(D34>B3)-D34
Then as you can see I have a value of 3.50, which is a decimal conversion of the 3:30 hours using
But because this is still typically a time object, if I was to multiply it by an hourly rate, I will not be returned with the correct pay.
What I need is to figure out a way to make that 3.5 a number that I can multiply and I simply cant remember how to cast it as an int properly.
I did try =INT(E2)+(E2-INT(E2))/0.6 but it looks ok, until you give it more than 2 decimal places, then it turns from 0.35 to 0.347
Any Ideas guys?
I am new to the site and have a question for you experts. I havent used EXCEL in a long time & recently decided to take a class to refresh my skills.
I have a problem that I must do and i know this is a really simple IF/THEN statement, but I just cant get it to work. I have cut/pasted the prob below...
A. Your spreadsheet must allow you to change figures that are given and still get the correct answer. Examples are tax rates, and hourly pay.
B. Employees are paid as either salaried or hourly, as indicated by the pay code. If the employee is salaried, the gross pay equals the pay rate. If the employee is hourly, gross pay is calculated by multiplying their hourly pay rate by the number of hours. However, if an hourly employee goes over 40 hours he gets 1.5 times his pay rate for the hours over 40. For this section use an IF statement.
Pay Code 1 - Hourly
Pay Code 2 - Salaried
This is where I get hagged up...I have the whole sheet set-up but CANNOT get the IF statement to work. I need to have one IF statement (maybe one imbedded in another) to calculate overtime if the employee has gone over 40 hours, i.e 41 in my problem.
How can I set up 1 formula to calculate the salraried employees pay as well calculate the hourly employees pay including overtime and ONLY have to change the hours and no other info in the sheet>
Can some one please guide me!?!?!?
Thanks in advance...
In one cell I have number of hours worked in time format e.g. 8:00 hours.
In the next cell I have the hourly pay rate in currencly format e.g. $22.50
In the next cell I want to be able to multiply 8.00 hours by $22.50 to get
the daily amount of pay but when I multiply the 2 cells I get some figure
which is way off. Does anyone know how I can do this ?
Using MS Excel 97.
Need help with writing a formula.
I have the following three (3) columns in a worksheet used to calculate labor-cost estimates for construction.
There are twenty (20) employee types (Emp Type). Each has a different hourly pay rate. Three (3) examples are shown. The employee types are designated using a coded combination of letters and numbers. This "code" is necessary for employee privacy purposes.
GF = General Forman @ 41.06 per hourly rate.
FM = Forman @ 39.51 per hourly rate.
LAB = Laborer @ 17.11 per hourly rate.
I wish to simply enter in the first column the >>Emp Type<<. Into the second column the >>Hours<<. The >>Amt<< colunm will calculate and display the result.
It seems to me that giving the >>Emp Type<< a "name" that refers to the hourly rate is the simplest result. How can I do this? I have tried the Insert, Name, Define menu options to no avail. I continue to get a #VALUE! error message.
I have a problem with trying to figure up how much to pay a telemarketer for the week. Our payroll spreadsheet is right now set up to figure up an hourly rate and a commission for the week. Whichever is greater, the salesman gets paid that amount. I found out that actually we've been paying wrong. What should happen is this: if the hours worked > 40 AND the commission > hourly rate, then it should be ((commission/hours) * .5 * overtime hours) + commission, else if either one of those is false then it should pay the greater of the commission or the hourly rate. I need to come up with a foolproof formula that will get me this amount. If there is anyone who can help me, I would greatly appreciate it.
doing a project spread sheet using custom format hh:mm to record volunteer
Problem 1 - volunteers who have contributed > 24 hours return the wrong
answer when hours totalled )i.e. seems to go back to zero when 24 hours
problem 2 how do I multiply the totla individual hours by the individual
hourly rates to obtain a total value?
Got a problem I hope someone can help me with:
I have several sheets where users can insert how many hours they have spent on a project. In this same sheet the user's hourly rate is inserted. These sheets look something like this:
Hourly rate - 100$
hours monday - 7,5
hours tuesday - 8
(The sheets for Bob, Dave, Eric and so on looks the same)
I also have a sheet that summarises time spent and the cost of that time. To find the time spent i use SUM(Start:End!B3) (B3 being 7,5 in Andys case above). This gives me the sum of all the hours spent in cell B3 (All the hours spent on monday). So far everything is fine. (Start and End are sheets located before and after the sheets for Andy - Eric).
However i wish to summarise the product of hours spent and hourly rate. I was hoping that SUMPRODUCT(Start:End!B3;Start:End!B2) would help me. It doesn't.
Looking forward to any possible sollutions on this one!
I'm trying to come up with a formula that will allow me to be able to the following overhead calculation that must be set every month to an hourly rate, that must equal the annual budget...
Annual Planned Work Hours = 51,750 [based on planned working hours of 1,125 working a 46 week year]
The total Budget = $388,000
There are 25 teams that work 45 hours per week = 1,125 [as above]
Because its based on a 46 week year recovered over 12 months = 46/12 = 3.8333 weeks
1,125 Weekly Hours * 3.8333 = 4312 monthly hours.
"still with me so far?"
So, $388,000 / 51,750 = $7.50 per hour recovery, which = $7.50 * 4,312 = $32,340 [which is planned each month].
However, the next monthly "hourly" rate needs to be set [the starting month being $7.50], but the planned working hours will change on a weekly basis. Some months may go up and some may go down. So, I need to understand what the rate formula for the rate should be, if....
Month 1: 4,312 hours @ $7.50 = $32,340 [which is as planned]
Month 2: 4,400 hours @ $7.50 = $33,000 [over recovered by $660]
Month 3: 4,450 hours @ $?.?? = i.e. what should this rate be??? Bearing in mind that we don't know the hours worked until the end of the month, I must know how to adjust the rate in Month 4, 5 and so on....
How do you Calculate the weekly pay for each employee. Remember there are salary and hourly employees. Hourly employees will be paid 1.5 times their hourly rate for any hours worked above 40.
1. Wilma's payrate is $150,000 yearly at 40 hrs.
2 John's payrate is $7.50 hourly at 44 hrs.
I'm hoping someone can help me. I am trying to build a formula that will determine an hourly wage. For example. If I make $1000.00 a week, and work 73.5 hours during that week, how can I create a formula that will determine for me the hourly rate up to 40 hours, and the overtime rate at 1.5X thereafter.....keeping taxes completely out of the picture. The only constant in the formula would be the $1000.00 per week, the hourly wage and overtime wage would constantly change depending on the hours worked for the week. Any help at all would definately be appreciated!
I am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.
For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.
For example a shift 07:00 - 21:00 would have:
1 hour 07:00 - 08:00 at Night rate
11 hours 08:00 to 19:00 at Day Rate
2 hours 19:00 to 21:00 at Night Rate
Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !
If anyone can offer any help I'd really appreciate it.
Apologies if this is a common one. I had a quick search but can't find anything that suits.
I'm looking for a formula that works out an overtime rate of 1.5 x hourly rate up to 36 hours but after that it goes up to 1.75.
I'd like to show the hours up to the 36 at one rate in one column and the higher rate in another.
I'm taking the totals from individual time sheets in separate sheets within the same workbook and totalling them in one sheet at the end.