Estimate number of overtime hours for a project

I'm an estimator.
I'm trying to create a formula in excel to calculate the number of overtime hours required to finish a project on time.
I have a set number of weeks to complete and a set number of hours to complete within that number of weeks.

I have created a simple formula that calculates the number of workers I need based on number of hour per week worked inside that number of weeks and number of estimated hours.
The weeks and hours cannot change.

Say I have a project to finish in 56 weeks and I have estimated 17,000 hours.
I can enter the number of hours I want to work to complete the project and it will populate the number of workers.
When I go over 40hrs week I'm having trouble creating the correct formula to calc number of OT hrs over 40hr week and subtract that from my estimated hrs.

I also don't know how to round up or down the number of employess. I can't have 7.58 or 6.07 employees although some do the work of .07 or .58 employess!

Does any of that make sense?

Here is what I have so far which isn't much.



Thanks for any help given!

Originally Posted by JBlunt918

I can't have 7.58 or 6.07 employees although some do the work of .07 or .58 employess!

I think every boss has some of those employees

To round to the nearest number of employees needed, you could use =ROUND(C9/A9/B9,0)

If you need to specifically round up or down, you can change the formula to ROUNDUP or ROUNDDOWN respectively.

To get total overtime hours, you could use =C9-(A9*B9*F9) which will show as a negative result if the number or employees is rounded up and the number of hours exceeds the estimate.

Thanks Jason for the help.

The rounding formula worked like I wanted it to.

The OT did not work as need but I came up with this.

=((A9*(B9-40))*F9)


which tells me the number of OT Id need to carry with 6 employees at 50hr weeks.

Now I need to figure out the amount of OT needed to complete the same job with a set number of employees.

Say I only have 5 guys to work this project thats 56 weeks long and only 17,000 hrs to do it.
How many hr/week would I need to complete it?


thanks again for the help

Came up with this on my own.

Works great.

=ROUNDUP((C17/A17)/F17,0)

thanks for the help!

Now I understand, my understanding of the overtime requirement was different to that which was expected.

I was looking at the shortfall of hours after multiplying weeks by hr/week by no of employees.

For the latest requirement, you would need to use a formula on the basis of =(17000/56/5)

Thanks all for the help!

I appreciate it.