I’m setting up some custom fields in MS Project to track progress of material submittals and need help with one of the fields that will have a formula.
Field 1 – drop down menu showing current status of submittal.
Field 2 – calendar entry field showing date status changed (user entered).
Field 3 – calendar entry field showing next expected status change date (user entered)
Field 4 – Current calendar date (auto filled)
Field 5 is my formula field. I want a formula that will show a graphical status (green,yellow,red) based on how close the field 3 date is to the current date.
– more than 5 days out, green
– 5 days or less, yellow, up to the field 3 date.
– beyond the field 3 date, red.
can someone help me with this formula? I’m just learning the custom fields, and getting deep into MS Project.
Is this too complex for a SWITCH formula? Would an IIF formula be better?
So far I get a lot of syntax errors on my formulas, so I’m hoping there are some Project experts that can help.
Thank you in advance.
Hi Robert. Welcome to extending Project functionality using custom fields. It adds a lot to what Project can do for you. I also like your thoughts on managing material submittals. Custom fields can be hard to get started but a little background and a lot of searching can go a long way. This formula is actually pretty simple. Use a Number field and simply subtract Field 3 from the current date: [Current Date] – [Field 3]. That will give you a number that you can use to set your graphical indicators: >5-Red, >=0-Yellow, Red. This should work as Project will take the first valid test. Good luck…
Thank you, that makes sense. I was making it more complex than necessary.
You are welcome, glad it helped…