Forum Discussion

Nicol860's avatar
Nicol860
Copper Contributor
Apr 09, 2021

IIf formula in microsoft project custom field

I added the following custom field with the formula: [Remaining Work] / [Remaining Duration]   Now I want to add an "if" condition, meaning if a task is 100% complete, that no value or the value "...
Dale_HowardMVP's avatar
Dale_HowardMVP
MVP
Apr 09, 2021
Nicol860 --

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.
  • Nicol860's avatar
    Nicol860
    Copper Contributor
    Apr 09, 2021

    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

    • Dale_HowardMVP's avatar
      Dale_HowardMVP
      MVP
      Apr 09, 2021
      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? 🙂

Resources