- 01/24/2019 at 5:11 am #414603
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.01/24/2019 at 9:00 am #414604
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…01/24/2019 at 12:30 pm #414607
Thank you, that makes sense. I was making it more complex than necessary.01/24/2019 at 12:49 pm #414609
You are welcome, glad it helped…