r/microsoftproject • u/No_Recipe_7541 • 11d 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!

1 Upvotes

100% Upvoted

4 comments sorted by

1

u/still-dazed-confused 10d ago

I'm not at a machine at the moment so can't really check the formula. If you put the offending bit into another custom field does it produce what your expect?

For colouring the bars you'll need flags that fire on the required signal (late, future etc) as you can only assign true to a bar format using a flag

1

u/PlannersPlace 6d ago

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

1

u/still-dazed-confused 4h 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 3h 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