none
Calculating Days based on quantity function RRS feed

  • Question

  • Hello!

    I am quite new to MS Project, and today I was trying to calculate days in Gantt Chart based on defined quantity, hours of work and pcs per hour.

    for example:

    Defined Quantity: 600 ([Number1])

    Pcs per hour: 3 ([Number2])

    Work hours: by default, 8hrs ([duration1])

    Work Time Length [duration2]= [Number1] / ( [Number2] * [duration1] )

    In calculator, it would show it'll take 25 days to reach to quantity 600, but for some reason MS Project shows 0.05 days, which I have no idea why and what went wrong, so asking your help!

    Thank you!

    -Tychelle

    Monday, November 5, 2018 8:27 PM

All replies

  • Tychelle,

    Well, you are enjoying the confusing conversion convention of Project's Duration fields. Project stores all time related data in minutes regardless of how that data is displayed in various fields. By using extra Duration fields in your formula, undesired conversions are coming into play. For example, using your values the formula should yield 25 BUT, those 25 "days" are actually in minutes so the result is divided by the number of minutes in a day which is 480. Thus 25/480 gives 0.052.

    Make life a whole lot easier for yourself by staying away from Duration fields in your formula and stick with Number fields

    Number4={Number1]/([Number2]*[Number3])

    But what exactly are you going to do with the result of your formula?

    Hope this helps.

    John



    Monday, November 5, 2018 10:33 PM
  • In addition to John's comprehensive answer, instead of dividing the minutes by 480, you can use the field [minutes per day] which is useful when working with customers who don't work 8 hours per day, or when you write formulas that you might use between different customers.

    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Tuesday, November 6, 2018 1:47 PM
    Moderator
  • Tychelle --

    In addition to the sage advice from my colleagues, just for fun I tried to recreate your situation.  From your description, it sounds like you are trying to calculate a ballpark estimate on how many work days it would take to produce a certain type of product.  In your example, the number of items to produce is known, and you known the production speed for producing that item.  To track the number of items to produce, I renamed the Number1 field as Required Quantity.  For the speed of production, I renamed the Number2 field as Pieces Per Hour.  Using your example, I entered 600 in the Required Quantity field and entered 3 in the Pieces Per Hour field.

    To determine the estimated duration to produce the number of items needed, I renamed the Duration1 field as Production Duration Estimate.  Now John and my cousin, Ben, have warned you that Duration values are stored in minutes, which is why you were struggling with your original formula.  To factor this information into my formula, I used the following formula in the Duration1 field:

    [Number1] * [Minutes Per Day] / ([Number2] * 8)

    Guess what?  The resulting value in the Production Duration Estimate field is 25d, which is the value you were hoping for.  I tried the formula with different values in the two custom Number fields, and the duration value is calculated correctly.  The duration value will be displayed with up to two decimal points of accuracy.  For example, to produce 1,000 items at a production rate of 8 pieces per hour, the resulting duration estimate will be 15.63d.

    So, maybe this is an approach you can use to resolve your own problem.  I did not mean to usurp the answers given by John and Ben; only to provide an additional thought for you.  Hope this helps.


    Dale A. Howard [MVP]

    Tuesday, November 6, 2018 9:32 PM
    Moderator
  • Tychelle --

    In my last post, I should have also mentioned that the value shown in the Duration1 column will have NO IMPACT on the value in the Duration column for the task.  Once you have your estimated duration values in the Duration1 column, you will still need to copy them from the Duration1 field to the Duration field.  There is no way to enter a formula into any default field in Microsoft Project, such as the Duration field, for example.  Hope this helps.


    Dale A. Howard [MVP]

    Tuesday, November 6, 2018 9:38 PM
    Moderator