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.
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
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
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
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