What formula to use for calculating OT hours for construction type spreadsheet.

[metalhead]

Hello all,

I have been tasked with assisting the project manager of my company to help him calculate a standard formula to calculate the OT hours for x amount of people/wages. What makes this kinda difficult, (honestly it's probably not THAT hard, but I am rather rusty on excel formulas l0l) is that the wages and hours and maybe a variable or two might change from time to time depending on the job and other factors.

 

[metalhead]

So to further expound on this, he just wants to be able to have a standard formula to calculate the OT hours at 1.5 the hourly wage rate. I just need a simple formula to do this and once I have it plugged in and have a better understanding, then i will be able to be self sufficient from there. Like I said it's been a while since I've geeked out on Excel or building spreadsheets with formulas for that matter. Any help or insight would be wonderful. Thanks!

 

[Roxxien]

Simple question for you, how would you calculate it with a pencil and a piece of paper?

If for exemple, each hour after more than 8 hour is overtime.
Then you do number of hour - 8 = overtime

In the end, it will something like this : = total hour - hours per day * number of day
I'm not sure of if it's good for you but =E8-H3*G3 would return 0 overtime.

 

[metalhead]

Yes I am aware it would return 0 for OT. What I was trying to get at was if those hours were say 56, I would like to know how to basically make the formula to calculate the difference from straight hours which would be 40 and then add time and a half (1.5*their wage) and then have Excel populate that on each employee line. If I take that formula, =E8-H3*G3 It worked on F8 but not on the rest. I'm apologize if I seem like a total noob but as I said it's been a while since I have played in Excel. Thank you for the response!

 

[Roxxien]

Did you try the formula without changing it?

Because if you want to increment it, you should use this one : =E8-$H$3*$G$3

 

[Roxxien]

Other thing, for your 56 hours with 40 hours of straight hour and 16 overtime, how to you get those number?

 

[metalhead]

That is just an average amount of hours usually worked. The picture I posted is somewhat of a template that will be used, however, I am needing to calculate the OT for each employee based on whatever hours they worked that week. Each job will have a proposed amount of days/hours that are agreed upon so not every employee will have OT. I just need a simple way to calculate their OT for any given job that they do. Again, I will not be needing the formula for OT calculation for everything, just the jobs that we know are going to take a long time to complete. So let's say that in E8-E13 all employees have 56 hours for that particular work week. Based on how this template was built, I need a formula that will take the difference after 40 hours are reached and populate that into the time and 1/2 cell to calculate the total amount of payroll.

Have I overly complicated my question? I am terribly sorry if I have.

 

[Roxxien]

In that case, I will suggest you again to take a piece of paper or an empty excel sheet to do what you want. No template, no formula just number and text. By doing that and calculating yourself what number of OT you need, you will see what would be the mathematical formula to do everything.

After that, it's easier to transform that into Excel formula and finish your template or ask a clear question.

 

[metalhead]

Travis OTFormula2 — imgbb.com

Ok so I think I understand how I might have not explained it correctly. If you look at the URL image, you can see all the formulas that are in place. We have to use this particular template for a particular contractor. My project manager does not have the time to calculate everything like that. We have around 70 employees and to just be able to plug in what hours were worked at X amount of wage, would save a LOT of time. I understand how to calculate on pen and paper, I just know that Excel is capable of producing this for us by plugging in the correct formula to do so. I apologize again if this is irritating or hard to understand.

 

[metalhead]

I have just had this literally thrown into my lap and I've been trying different formulas and doing research, but I cannot seem to find a formula that will calculate this the way its supposed to.