MS Project - RAG Status

Anonymous
2022-04-20T16:44:35+00:00

Hi

I’m try to create an IIf formula in MS Project 2016, but just can’t get it right. Can anyone help? I want to do the following:

Finish date is = or >than baseline finish it’s Red

If finish is 10 days to 1 days before baseline finish it’s Amber

If finish is 11 days or more than baseline finish it’s Green

If task is 100% complete it’s blue

Or similar to above, open to suggestions. Any help much appreciated.

Microsoft 365 and Office | Project | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. John Project 49,715 Reputation points Volunteer Moderator
    2022-04-21T20:19:06+00:00

    CathyBryant18,

    In your initial post you said you wanted to compare finish with baseline finish but that's not what your formula shows. Is the formula you are showing just an test example?

    When you say it's not working, what does that mean? Is the formula showing an error (i.e. can't even get it to execute)? Does the formula execute but not give the results you expect? If so, a screen shot of a few representative tasks with the % Complete, Start, Finish, and Baseline Finish fields showing along with the custom field with your formula would help.

    What field are you using for your custom formula (e.g. Text, Number, etc.)?

    I did a quick check on your formula and it showed a syntax error. The second criteria showing "=>" should be ">=".

    John

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-04-21T19:10:08+00:00

    Hi John

    Thanks for getting back to me, I tried this one out but couldn’t get it to work. Any suggestions? Maybe what I’m asking for is not the right way.

    IIf([% Complete]=100,4,(IIf([Current Date]=>[Finish],3, (IIf([Current Date]>[Start] And [% Complete]=0,5,(IIf([Current Date]>[Finish]-14 And [% Complete]<80,2,(IIf([Current Date]<[Start],6,1)))))))))

    Was this answer helpful?

    0 comments
  3. John Project 49,715 Reputation points Volunteer Moderator
    2022-04-20T20:05:11+00:00

    CathyBryant18,

    It would be helpful to see what formula you have. Keep in mind that formulas evaluate from left to right. The first test should be whether a task is already complete because a complete task may also meet the other criteria so if the 100% complete is the last item in the formula, it will never show as blue.

    I'm not sure why you want a task whose finish date is equal to the baseline finish should be red since that's the optimum condition. However a finish date later than the baseline finish should be red.

    On your second criteria I'd say if a task finish is less than 10 days before the baseline finish, it is amber. That includes being equal to the baseline finish.

    If not of the above it true, then the task is green.

    Hope this helps.

    John

    Was this answer helpful?

    0 comments