What is the specific Custom Fields Formula using (GREEN, YELLOW and RED) indicators for Task Start and Finish Dates?

Question

What is the specific Custom Fields Formula using (GREEN, YELLOW and RED) indicators for Task Start and End Dates as follows in MS Project?

• 1 Task Start Date is within 5 or more of the Task Start Date = GREEN indicator.
• 2 Task Start Date is within 2 to zero days of the Task Start Date = YELLOW indicator.
• 3 Task Start Date has Not Started On Time = RED
• 4 Task Finish Date is within 5 to 2 days of the Task Finish Date = YELLOW indicator.
• 5 Task Finish Date is within 1 to zero days of the Task Finish Date = RED indicator.
• 6 Task Finish Date has Not Finished On Time = RED

Answer 1

I would suggest abandoning any idea of using these type of indicators as they will add nearly zero value to your schedule analysis.

Read up on the critical path. Variances that accrue at the package level, or lower if you track that, only matter when it is on the critical path. Other packages off the critical path can be late all day with no effect on your project.

Having stop light indicators will create a ton of noise that WILL confuse everyone who sees it. It WILL cause the team to chase the yellows and reads no matter if these variances are material or not.

And to confuse matters more, the critical path(s) constantly changes. So a package NOT on the CP last month could very well be on the CP this month.

Indicators of variance are very useful, but you need the right ones.

Answer 2

TL;DR

"Please program my custom dashboard for me" would be an off-topic request anywhere on Stack Exchange, so I'm going to address myself to the meta-questions you're asking about how to build the custom fields, and how to manage that effort within your project. I think your implicit meta-questions are good ones, and merit a thoughtful answer.

MS Project Custom Fields

Microsoft Project allows you to create formulas and graphical indicators. A rather thorough description can be found in various places on MS Developers Network (MSDN) and in the MS Office online help. A couple of places to start include:

1. Using Formulas and Graphical Indicators with Custom Fields
2. Project functions for custom fields

Custom Programming of Dashboards is a Project Deliverable

Custom things, by definition, are rarely things other people have needed to put together in exactly the same way. If everyone was using them, they'd be standardized into a template, drop-down, or wizard.

As for your list of desired formulas, it's possible that someone already has some related macros they can share, but I rather doubt it—especially if you're looking for a complete series of custom formulas. If they don't, then you need to treat that list as a custom programming requirement for your project and add that effort to your schedule.

Account for Work on Project Tools

CodeGnome's Law of Transparency says "No invisible work, ever!" In a tangential post, I explain that:

Work is work, whether it's on bugs or new features. Therefore, all work needs to be tracked on the Sprint and Product backlogs, regardless of the source.

Even if you aren't running an agile project, effort expended on your project management tools and controls should be charged against the project. If this effort were trivial, you it would already be done and you wouldn't be asking about it here. That indicates that it's a process requirement that has a non-zero cost to the project. Account for it!

Answer 3

If you are trying to indicate upcoming starts, upcoming finishes, and lateness, then here is a formula to get you started:

IIf([Start]-[Current Date]>=5,"Green",IIf([Start]-[Current Date]>=0,"Yellow",IIf([% Complete]>0,"Blue","Red")))

Insert this formula into a custom text field, and it will display various outputs under the following conditions:

1. "Green" if the current date is greater than or equal to 5 days ahead of the start date.
2. "Yellow" if the current date is less than 5 days and greater than or equal to 0 days ahead of the start date.
3. "Blue" if the current date is past the start date and the task % complete is greater than 0 (i.e. the task has started).
4. "Red" if the current date is past the start date and the task % complete is 0 (i.e. the task has not started).

You can use similar logic to create another custom calculated field based on the finish date.

Answer 4

To help you boil it down a bit.
As you have multiple criteria you can use either and IIf comparison function or the Switch function.

IIf(condition to be tested, result if true, result if false)

Switch (condition to be test, result if true, next condition, result if true, next condition, result it true)

To perform math with dates in Project: ProjDateAdd or DateAdd functions.

Try a search on "RAG indicators in Project" to see some examples.