Forum Discussion
IIf formula in microsoft project custom field
To create a formula that works correctly, I recommend that you use a custom Text field. That is because you want the "n/a" text to appear when a task is 100% complete. Also, you need the formula to test for a milestone, as the formula will result in a division by 0 error. Following is the updated formula, which I tested and confirmed that it works correctly in a Text field:
IIf([Milestone],"n/a",IIf([% Complete]=100,"n/a",[Remaining Work]/[Remaining Duration]))
Hope this helps.
Dear Dale_HowardMVP, thank you very much for your help!
Unfortunately I can't get it to work, but do you know some resources (books, websites etc.) where I can learn more about how to write formulas in MS Project? I can't find much information online, does there just not exist much information?
Thank you very much for your help!
Kind regards,
Nicola
- Nicola -- "Unfortunately I cannot get it to work..." does not give us enough information to help you further. Did you copy and paste my formula into a task Text field? If so, the formula should have worked immediately. What did you do in response to my last message? And if it is generating an error, please include one or more screenshots to show us what you see.
Also, to the best of my knowledge, the only book on Microsoft Project formulas is in Portuguese, and was written by a colleague of mine. Do you speak/read Portuguese? 🙂Dale_HowardMVP Yes, I copied your formula and also tried typing it in and picking the fields & operators, but it gives me an error message (see screenshot attached).
The difficulty is that it does not indicate where the error is, I tried if a simple IIf formula works, but not even that I can get to work.
So far only the formula [Remaining Work]/[Remaining Duration] has worked on its own (as a number type).
With the IIf formula I want to achieve that the project managers see when a task is complete, so they don't need to pay attention to these tasks when they assess how much work still is to be done in the remaining time for a task. So it does not necessarily need to show "n/a" it could also show something else (like a number or a flag)
