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
• 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.

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

Written by Dave Gordon

Dave Gordon is a project management consultant with over twenty five years of experience in implementing human capital management and payroll systems, including SaaS solutions like Workday and premises-based ERP solutions like PeopleSoft and ADP Enterprise. He has an MS in IT with a concentration in project management, and a BS in Business. He also holds the project management professional (PMP) designation, as well as professional designations in human resources and in benefits administration. In addition to his articles and blog posts, Dave curates a weekly roundup of articles on project management at The Practicing IT Project Manager, and he has authored or contributed to several books on project management.

1. Dave,

I am just curious: You state: “summary tasks can have some rather complex roll-ups, so I don’t apply it to them.” Why don’t you use the % Complete that Microsoft Project calculates on Summary Tasks? As I understand it, it is simple and straightforward:
% Complete on Summary Task = (sum of Actual Durations on its detail tasks)/(sum of all durations-to-the-status-date of all its detail tasks)

I see no reason why your formula would not work on Summary Tasks; am I missing something?

Eric

2. Hi Eric,

The formula assumes an even distribution of effort between the Start and Finish dates. This is a reasonable assumption for Detail tasks, but Summary tasks can easily have component tasks that are “clustered” in time. As a result, there can be an uneven distribution of effort over the period between the Start and Finish dates. For example, a Summary task which rolls up several one-week tasks that proceed in parallel, followed by one two-week task, should be expected to “mostly” done at the end of that first week. In this case, if one or more tasks are running behind, the calculated Summary % Complete can be as little as 34% and the calculated status will be green since there would be two more weeks until the Finish.

3. Hi, this is great…i have one problem…on only SOME of my summary tasks I still get a higher number instead of the 1 — summary task shows a color instead of a white circle. I do not have roll-up set on the custom field. Any response appreciated. This is the best formula I’ve found out there…thank you!

4. My apologies, I’ve reapplied the formula and no longer seeing any rollups. thanks.

5. I’ve applied the changes and checked the formula. The indicator only appear for certain tasks. Most of them say #ERROR in the field. Suggestions on what to check. This is with MS Project Online Desktop Version 1808, being run from Office 365.

6. James, check Project|Project Information and make certain you have a date (rather than “NA”) in the Status Date. I missed that.

Otherwise, double-check the formula.