Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Custom Duration Formula Help Needed

4 replies [Last post]
Chris Lothian
User offline. Last seen 48 weeks 1 day ago. Offline
Joined: 18 May 2007
Posts: 34

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.

Replies

Luc Overdulve
User offline. Last seen 13 years 4 weeks ago. Offline
Joined: 20 Feb 2006
Posts: 5
Groups: None

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

Rafael Davila
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 1 Mar 2004
Posts: 5229

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

Chris Lothian
User offline. Last seen 48 weeks 1 day ago. Offline
Joined: 18 May 2007
Posts: 34

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

Rafael Davila
User offline. Last seen 2 weeks 5 days ago. Offline
Joined: 1 Mar 2004
Posts: 5229

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