While Project includes a Status field, it’s not always exactly what I want. I use a numeric field to calculate the status of detail tasks in the project plan, and display stoplights—Green, Amber, and Red—based on the calculated value. I’ve found it adds some immediacy to project status meetings. Note that this formula produces reliable results for detail tasks, but summary tasks can have some rather complex roll-ups, so I don’t apply it to them.

Here’s what this status field 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 Amber

For non-zero duration tasks
- If % Complete = 100, return Blue
- If it’s not scheduled to start yet, return Clear
- If past the scheduled finish date and % Complete is less than 100%, return Red
- If % complete >= the prorated expected completion, return Green
- Otherwise, return Amber.

That “prorated expected completion” is calculated using the project calendar, so it takes non-working time like holidays into account. 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 Amber; otherwise, it will be Green.

Adding the Stoplight Indicator

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, and that’s fine because you really do want to calculate the values.

Then click on the Formula button, and paste in the following formula:

IIf(Duration=0,(IIf([% Complete]=100,5,IIf(Finish<[Status Date]+7 And [% Complete]<100 And [% Complete]>=80,3,IIf(Finish<[Status Date]+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5,IIf([% Complete]>=100*(Abs(ProjDateDiff(Start,[Status Date])/ProjDateDiff(Start,Finish))),2,(IIf(Finish>[Status Date],IIf(Start>[Status Date],1,3),4)))))

Note the brackets around the fields “% Complete” and “Status Date.” This is because of the embedded space in the field name.

Click OK.

Now click on the Graphical Indicators button. This is where you choose the colors to display. In each row, under “Test for” select “equals.” Associate the values in the second field with the images in the third field, like this:

5	Blue	Completed
4	Red	Past finish date
3	Amber	Behind expected progress
2	Green	Progressing on track
1	Clear	Future task

Note that you only want these indicators to appear for non-summary rows, since the % Complete field isn’t accurate for summary rows. Click OK.

Using the Stoplight Indicator

In the formula, we reference a field called Status Date. This is set at the project level, under the Project tab. In this case, I’ve set the status date to May 21.

The formula will compare the status date to the Start and Finish dates for each task, and based on % Complete, determine whether a task scheduled to be “in progress” is in Red, Amber, or Green status. Consider this example:

The first two tasks are Blue because they are 100% complete. Task 3 is Red because it’s two days past the finish date and it’s only 75% complete. Task 4 is Green because two days have passed, the task was scheduled for three days, and it’s 75% complete. If the task were less than 67%, it would be Amber. Task 5, a milestone, is Amber because it’s only 80% complete, and near the Finish date. All the subsequent tasks have Start dates after the Status date, so they have a clear indicator. Remember that “days” is defined using the project calendar, so it only uses working days.

Final Thoughts

As you can see, this is a rather complex formula. The calculations will only be meaningful if the % Complete information it’s based on is maintained on a consistent basis. Set some standards for the values you enter. For example:

Milestone tasks: Enter either 0% or 100%, depending on whether all predecessor tasks are complete. Alternatively, enter 0%, 25%, 50%, 75%, and 100% as predecessor tasks are completed.

Detail tasks: Enter 0%, 25%, 50%, 75%, and 100%, as the task is worked. Alternatively, enter other values, appropriate to the task and the performing team members.

Whatever standard you choose, communicate it to the project team and use it consistently. Don’t fall into the trap of starting a task at 90% and then spending the next few weeks waiting for the performer to report 100%! Define “inch pebbles” that can be reported at the task level. If necessary, break the task into multiple tasks so that the durations are in a smaller, more meaningful range. A consistent granularity is the key to measuring and reporting progress.

If you think of other variations that might be useful, such as a simpler version of the prorated expected completion calculation, leave a comment below.

Related Content

Webinars (watch for free now!):
Advanced Tips for Resolving Resource Over/Underallocation
Eliminate the Confusion – Deep Dive into Task Types and Effort Driven

Articles:
Exploratory vs. Explanatory Visuals in Planning
Microsoft Project Do’s and Don’ts: Enter Task Durations
When a Fixed Duration Task is Truly Not “Fixed Duration”