+ Reply to Thread
Results 1 to 7 of 7

Estimate number of overtime hours for a project

  1. #1
    Registered User
    Join Date
    07-10-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    7

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Estimate number of overtime hours for a project

    Quote Originally Posted by JBlunt918 View Post
    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.

  3. #3
    Registered User
    Join Date
    07-10-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Estimate number of overtime hours for a project

    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

  4. #4
    Registered User
    Join Date
    07-10-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Estimate number of overtime hours for a project

    Came up with this on my own.

    Works great.

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

    thanks for the help!

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,027

    Re: Estimate number of overtime hours for a project

    In E9 then drag down

    =FLOOR(C9/(A9*B9),1)

    In F9 then drag down

    =C9-(A9*B9*E9)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Estimate number of overtime hours for a project

    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)

  7. #7
    Registered User
    Join Date
    07-10-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Estimate number of overtime hours for a project

    Thanks all for the help!

    I appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis
    By HumdrumPanic in forum Excel General
    Replies: 5
    Last Post: 09-30-2020, 12:55 PM
  2. Forecasting Overtime Hours Required to Complete a Project
    By VAred in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2015, 11:04 AM
  3. How to Calculate Number of Overtime Hours but Only for Weekday
    By ajyoo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2014, 03:18 AM
  4. How to Calculate Number of Overtime Hours but Only for Weekday
    By ajyoo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-16-2014, 02:31 AM
  5. How to Calculate Number of Overtime Hours but Only for Weekday
    By ajyoo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2014, 07:51 PM
  6. [SOLVED] Help with determining the number of regular vs. overtime hours within a set time frame
    By niftysquirrel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 12:52 PM
  7. [SOLVED] How to project the due date according to a number of working hours
    By Eric in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-28-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1