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]

 

[metalhead]

Here are all the formulas in place. Due to how this is setup, I am having issues trying to figure out the correct way to make a formula for what is presented here.

 

[Roxxien]

Ok, for your formula in E8, I suggest you to add * number of men so =$G$3*$H$3*C8

The sum formula have no use when you are working with only 1 value.

So G8 should be =SUM(C8*D8*E8,D8*F8*1.5)*G6/100
H8 should be =SUM(C8*D8*E8,D8*F8*1.5)+G8

For your OT zone, you have the straight number of hours with a formula.
However, how many hour in TOTAL did you worked? If you don't have that value how can you know for the OT part?

EDIT : forgot the times 1.5 for OT

 

[metalhead]

Ok so here is a better example of what this sheet would normally look like on any given week at a job:

[/IMG]

So now how do I get the OT amounts to populate in the OT cell?

 

[metalhead]

Now here is the same thing but with your formulas plugged into it:

 

[Roxxien]

Your first formula only worked for 1 employee for each line, this is why you now have different number.

For your OT, I will ask again the same question, where do you have the total number of hour worked?

We need that value to calculate the OT times by doing total minus straight.

 

[metalhead]

Firstly, let me just say thank you Roxxien, thank you very much for having patience and providing the help and insight that you have. Secondly, let me apologize for not understanding enough of what I had to work with and my lack of knowledge about formulas, in order to give you a better explanation. Can't make bricks without clay right? : )

I do not have a set amount of the total number of hours worked. This is just a template that we are going to use to track hours/wages/costs/materials/etc. In the last image I posted, the cell that says "Straight Time HRS" would be the total number of hours worked for that crew. Cells B8-B13 would all be different crews with different hours each. The numbers I put in there are just some averages of the number of hours we usually work. The amount of "Straight Time HRS" is the total amount worked for that particular week/job, and, therefore, would be the total number of hours worked.

I really hope that answers your question. If not, please let me know because what you have helped me with so far has been a tremendous help and like I said earlier, I very much appreciate your help and patience.