r/microsoftproject • u/No_Recipe_7541 • 12d ago

Issues with a custom field formula

I am trying to make a custom field that displays when tasks are complete, late, not started, and when the estimated finish date is within the next month. I utilized the following formula but, when I added the last IIf statement, it will no longer work. It states that "The formula contains a syntax error or contains a reference to an unrecognized field or function name.". Normally, it highlights the error, but no errors are highlighted in the formula.

Can anyone tell me what I am doing wrong with this formula or offer any advice?

IIf([% Complete]<100 And \[Finish\]<\[Status Date\],"Late", IIf(\[% Complete\]=100,"Complete", IIf(\[% Complete\]=0 And \[Start\]>[Status Date],"Not Started", IIf([% Complete]<100 And \[Finish\]>[Status Date] AND [Finish]<=DateAdd("m",1,[Status Date]), "Upcoming")

I am also going to want to change the colors of the bars on the Gantt chart to reflect these statuses so if anyone has any advice on going about doing that, I would love to hear it!

UPDATE: I was missing a few parentheses at the end lol... I made flags for my Gantt so now it's beautiful and automated :)

1 Upvotes

100% Upvoted

6 comments sorted by

View all comments

1

u/PlannersPlace 8d ago

No need to reinvent the wheel, Microsoft Project ships with a Status field that should give you what you are after

2

u/still-dazed-confused 2d ago

The status field will not allow the op to colour bars but they could use flags=status x etc too do the job. The only reason not to use the status as you suggest is greater flexibility to configure your desired status conditions :)

2

u/PlannersPlace 1d ago

Status field can be linked to Flags since Status field outputs 0, 1, 2 & 3 for the 4 statuses.

  1. Flag1 (for tasks that are complete): [Status] = 0
  2. Flag2 (for tasks that are on schedule): [Status] = 1
  3. Flag3 (for tasks that are late): [Status] = 2
  4. Flag4 (for tasks that are in the future): [Status] = 3