Skip to content
ProjectControls.com
Microsoft Project

Custom Duration Formula Help Needed

In the Open Standard
Chris LothianMember for 19 years 5 months
Created
4
3
0

Hi all,

I am currently working in MS Project 2003 and need some help with setting up a formula in a custom duration column.

I have a text collumn (Text12) which has 4 possible values from a value list:

Major Works

Standard Works

Minor Works

Immediate Works

I now want a duration column that reflects the following;

Expression                                                         Duration Value

Iif((Text 12) = "Major Works"                                   -65 days

Iif((Text 12) = "Standard Works"                              -10 days

Iif((Text 12) = "Minor Works"                                   -3 days

Iif((Text 12) = "Immediate Works"                            +2 hours

Iif((Text 12) = ""                                                     ""

I think i am nearly there but it isn't working, this is what i have so far;

Iif((Text 12)="Major Works",-65 days, Iif((Text 12)="Standard Works",-10 days,Iif((Text 12)="Minor Works",-3 days,Iif((Text 12)="Immediate Works",+2 hours,""))))

I believe i need it as a duration as i want to then use it in another custom date collumn & with bar formatting, but any other suggestions are welcome.

Save Follow Follow forum Report
Reply New topic

4 replies

Oldest first
  1. 0
    Rafael Davila ·
    #1

    Chris, Try IIf((Text12)="Majo

    Chris,

    Try

    IIf((Text12)="Major Works",-65*8*60,IIf((Text12)="Standard Works",-10*8*60,IIf((Text12)="Minor Works",-3*8*60,IIf((Text12)="Immediate Works",2*60,0))))

    in a Custom Duration Field.

    Do not leave spaces betewwn field names Text12 is not the same as Text 12. Also your duration formulas might be dependant on your calendar settings. I am using an 8 hours work day.

    Note I am assuming your logic to be good as I do not understand what you want to do and negative duration values are kind of unusual.

    Regards,

    Rafael

    Report
  2. 0
    Chris Lothian ·
    #2

    Thanks Rafael, The end

    Thanks Rafael,

    The end product that i want is for in another column to display the date at the task start date plus the custom duration (therefore a negative duration will then display a start date prior to the task start date). I also want to display a flag on the Bar Row at this time (but without it being an actual task).

    Is it posible to do this calclution (to get the custom date) all in 1 formula, therefore freeing up some more custom columns?

    Chris

    Report
  3. 0
    Rafael Davila ·
    #3

    Chris, I believe it can be

    Chris,

    I believe it can be done, perhaps some functions to change data type will be needed but same as I got the formulas, you can try by trial and error, if stuck again let us know.

    I am not an expert MS Project user as I use spider Project but I learn a lot by following what you do with MS Project, I frequently make use of formulas within Spider Project, we have unlimited user fields creation so I can literally use hundreds or thousands of custom fields. However there are some differences on how the two software work with formulas as Spider allows for recursive formulas.

    Now I cannot live without formula functionality, instead of hundred predefined fields I define my own as need be.

    Regards,

    Rafael

    Report
  4. 0
    Luc Overdulve ·
    #4

    Hi Chris,Note that field

    Hi Chris,

    Note that field names have to be enclosed with brackets like [text12]. Also note that the result will be 0 when none of the given entries will be found, because of the use of a Duration field...


    I prefer a formular like this, because it is not that critical interpreting the [text12] contents : 

    IIf(instr(1;[Text12];"major";1)<>0;(65*8*60);IIf(instr(1;[Text12];"standard";1)<>0;(10*8*60);IIf(instr(1;[Text12];"minor";1)<>0;(3*8*60);IIf(instr(1;[Text12];"immediate";1)<>0;(2*60);0))))

    I omitted the negative signs... 

     

    Regards,  Luc

    Report

Log in or join to reply

ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.

Log inJoin free

Existing members: your original username and password still work. Forgot password?