1

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
  • Warning: MSP looks like it supports this kind of functionality,but every time we've tried it, we've spent more time maintaining the custom fields formula than we have managing projects. MSP is a lousy development environment. – Mark C. Wallace Sep 5 '13 at 10:48
  • Thanks Mark for your feedback. :-) I have 18 plus years experience in "Project Management" so what I'm looking for is for someone to share with me the specific Custom Fields Formula (as per my Question / Request). I'll make the appropriate decision as it pertains to the "project" as to whether my approach is useful for this "specific project" or not. Though I appreciate the other feedback and comments...what I'm looking for is the specific Custom Fields Formula (as per my initial Question / Request. Thanks – jgoodm03 Sep 5 '13 at 13:47
  • @jgodm03: I'm so glad you informed us that you have 18 years of experience. We really, really needed to know that. – Andrew Clear Sep 7 '13 at 19:37
3

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.

  • Thanks David for the expeditious response. While I agree with you on the one hand regarding tracking the project's Critical Path, I still see (Custom Field Formula) Indicators as useful to know when tasks are coming due and/or are past due. It is easy to hide the "Indicators' from the "Project Team Participants / Members" who would not benefit from knowing the Task Indicator conditions. Because of this fact, I still see the MS- Project "Custom Fields Formula" Indicators as useful. Thank You. :-) – jgoodm03 Sep 4 '13 at 21:30
  • I agree with David. You can make all sorts of handy selections in MSP to focus on tasks that are past finished date, due to start and/or finish next week etc. It's better to focus on each 'status' of tasks separately, then to see everything in one view with lots of colors that might mean different things depending upon the status of the task. – Stephan Sep 5 '13 at 9:48
  • Thanks David for your feedback. :-) I have 18 plus years experience in "Project Management" so what I'm looking for is for someone to share with me the specific Custom Fields Formula (as per my Question / Request). I'll make the appropriate decision as it pertains to the "project" as to whether my approach is useful for this "specific project" or not. Though I appreciate the other feedback and comments...what I'm looking for is the specific Custom Fields Formula (as per my initial Question / Request. Thanks – jgoodm03 Sep 5 '13 at 13:48
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!

2

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.

  • 1
    Welcome to PMSE! I've added a bit of formatting to your post. Meanwhile, please note that signatures in answers are discouraged, but it's fine to place them in your profile. – Todd A. Jacobs Sep 16 '13 at 15:10
  • Thanks Tony I'll check out your approach / solution. :-) I actually discovered the following Project Custom Fields Formula does the basic rudimentary things I need at this time for this project. IIf([Finish]<=Date() And [% Complete]<>100,2,IIf([Start]<=Date() And [% Complete]=0,1,IIf([% Complete]=100,3,0))) The Graphical Indicators Needed in MS Project Value 0 = Green Status Indicator = Task Is On Schedule Value 1= Yellow Status Indicator = Task Not Started On Time Value 2= Red Status Indicator = Task Not Finished On Time Value 3= (Clear Circle) -Task is 100% Complete. – jgoodm03 Sep 25 '13 at 19:50
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.