An improved version of this article is included in my free e-book, MS Project Hacks. Download the book and a sample MS Project file demonstrating the techniques shown here.
About a year ago, I posted an explanation of how to create a “stop light” status indicator using calculated fields in MS Project. It has since become the most widely read, referenced, and commented-on post I’ve ever made. So, based on some of those comments and a request from Marlene Robertson, I’ve decided to post an update. Here’s what it does:
- For zero duration tasks (milestones)
- If % Complete = 100, return Blue
- If the scheduled finish is at least seven days away, return Clear
- Otherwise, if % Complete is < 80, return Red
- If % Complete is between 80 and 99, return Yellow
- For non-zero duration tasks
- If % Complete = 100, return Blue
- If it’s not scheduled to start yet, return Clear
- If it’s past the scheduled finish date, return Red
- If % complete >= the prorated expected completion, return Green
- Otherwise, return Yellow.
That “prorated expected completion” is calculated using the project calendar. If the percent complete is less than the number of days since the task was scheduled to begin, divided by the task duration, it will be Yellow; otherwise, it will be Green. Keep this in mind for later, when we’ll talk about summary tasks.
To add this to your project schedule, right click on the column where you want to insert the status indicator. Click “Insert Column” and then select Number 1. Right click on the newly inserted column and select “Custom Fields.” From here, you can rename the field (I chose “St” to keep it brief). Under custom attributes, click on the radio button for Formula. You’ll get a warning message; that’s fine, because you really do want to calculate the values.
Then click on the Formula button, and past in the following formula, if you are using MS Project 2007 or earlier:
IIf([Duration]=0,(IIf([% Complete]=100,5,IIf([Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80, 3,IIf([Finish]<Now()+7 And [% Complete]<80,4, 1)))),IIf([% Complete]=100,5,IIf([% Complete]>=100*(Abs(ProjDateDiff([Start],Now())/ProjDateDiff([Start],[Finish]))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3), 4)))))
If you are using MS Project 2010 or later, the field names have changed. Use the following formula:
IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5, IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5, IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))
Click OK, and then click on the Graphical Indicators button. This is where you choose the colors to display. In each row, for Test select “is greater than or equal to.” Associate 4.5 with Blue, 3.5 with Red, 2.5 with Yellow, 1.5 with Green, and 0.5 with Clear. Note that you only want these indicators to appear for non-summary rows, since the % Complete field isn’t accurate for summary rows. More about that in a moment.Then click OK. Your project plan should look like this: On August 25, Task 1 is Blue because it’s complete. Task 2 is Yellow because it’s two days past the start date, it’s a three day task, and it’s only 50% complete. Task 3 is Green because two days have passed, the task was scheduled for five days, and it’s 75% complete. If the task were less than 40%, it would be Yellow. Task 6 is Red because it’s incomplete, and past the Finish date. The start date for Child Task C is in the future, so it’s Clear. Note that “days” is defined using the project calendar, so it only uses working days.
You’ll see that no indicator is shown for Task 5, which is the summary task for Child Tasks A, B, and C. For automatically scheduled summary tasks, MS Project shows the Start date as the earliest Start date among its children, and Finish date as the latest Finish date. It calculates Duration for its child tasks as the difference between the two. In the simple example above, the child tasks are scheduled in the sequence A, B, C so that the summary task duration actually reflects the sum of the durations of the individual tasks, ten days. Task A is scheduled for two days, and is 25% complete; Tasks B and C are not started. Based on this, summary Task 5 calculates 5% complete, which is accurate.
Now consider if Task A and Task B were scheduled to start on the same date. The Duration for Task 5 would be calculated as eight days, although it still represents ten work days of effort. After two days, you would expect Task A to be complete, Task B to be at 50%, and Task C to not yet have begun, for a total of four days of effort. Thus, anything less than 40% complete for the summary task should appear Yellow, but the formula will show Green at 25%. So rather than display a potentially inaccurate result, we simply don’t show stop lights for the summary tasks.
Give it a try, and leave a comment if you find it useful, or find a bug, or think of an enhancement.