Custom Fields

[Robert Gambill]

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.

[Larry Christofaro]

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…

[Robert Gambill]

Thank you, that makes sense. I was making it more complex than necessary.

[Larry Christofaro]

You are welcome, glad it helped…

[Author]

Posts