Duration Formula RRS feed

  • Question

  • Hi,
    I am trying to create a formula to calculate duration for projects
    Our business is to produce highly customized products and each goes through different production process,
    We have over 40 different products and each goes through some of the 11 production processes which each have a different duration depending on the type of product
    What I am trying to do is to create a formula that depending on the product type and quantity to calculate the time for each step
    I have tried the following formula yet it is giving me an error
    IIf([Text20]="SC Interior Set" And [Text19]="Router",([Duration1]=([Number20]*3)),"Wrong")
    Text20 is Product Column
    “SC Interior Set” is a Product name
    Text19 is Process Column
    “Router” is a production process
    Number20 is the Quantity Column
    3 is the time for this (SC Interior Set product router time in hrs)

    Kindly advice
    Thank you


    Wednesday, August 1, 2012 7:28 PM

All replies

  • Okay ... so what's the problem? What happens when you do his? What is the error message? In what field are you putting that IIF forumula? What do you do with the results of the computatation? Are you sure you shouldn't be using something like Excel to do these computations before you put into Project?


    Wednesday, August 1, 2012 8:59 PM
  • Hi,

    What do you want to appear in your field when the iif condition is true?


    is not something that can be evaluated.

    If the field you are using is duration1, then just put Number20]*3)

    If it is an other field, note that you cannot change a field (Duration1) through a formula in an other field

    If you want this to be interpreted as a boolean value, you need an IIf.

    Hope this helps,

    Thursday, August 2, 2012 7:21 AM
  • If you have 40 products and 11 processes, you'll have 440 different combinations. MSP only has a limited numbers of custom fields to do this evaluation, so no matter what you could only do 5-10% of the combinations in MSP.

    Maybe that is sufficient for your purpose, but as Rob suggest Excel with some LOOKUP-formulas, etc. is probably a better way to go.



    • Edited by basejump Friday, August 3, 2012 7:17 AM
    Friday, August 3, 2012 7:11 AM
  • Hi,

    In fact, he doesn't need 440 fields. He has to put the formula in the duration1 field then combine the iifs. However, he will be limited by the maximum length of a formula. The best alternative for doing this in Project is a VBA procedure - which by the way can write directly in Duration, no need to pass through a custom duration field.

    And yes, the code will have hundreds of lines but that is inevitable with this combination of fields.


    Friday, August 3, 2012 8:21 AM