Schedule automation: How to customise MS-Project® text fields to calculate RAG Status

Part 2 of a series on customising fields in Microsoft® Project® to make working with project schedules more useful.
So you’ve created a useful programme schedule, and you are now working on keeping it on the right track, reviewing the plan regularly with the Programme Manager and Project Managers. You will probably review the programme schedule regularly – focussing on on items with Red or Amber RAG status –  but how do you use MS-Project to ensure that you don’t skip items that probably should have been marked amber or red?

Schedule Automation - How to customise MS-Project® text fields to calculate RAG Status

This post describes how to introduce some schedule automation by customising a text field to display RAG status (based on performing some simple tests and calculations with dates).

So why would you want to use this approach, how do you do it, and what’s in it for you as a result?

The Why

RAG status is often allocated to project tasks based on the intuition or “gut feel” of the project manager. But too much subjectivity in status reporting can lead to watermelon or green-side-up reporting, in which project status is reported more favourably than it probably should be. The more objective approach presented here introduces some schedule automation, assigning a RAG rating to individual project tasks based on calculation.

This approach can’t account for subjective things like project risks (that takes professional judgement), but it can give a useful “starter for ten” RAG status assessment onto which the PM can layer factors such as risk, or to act as the starting point of a discussion.

The How

Approach: Logic tests

Devise a set of criteria that you will use to determine RAG status, based on the rules or criteria in place for your project or programme. Your central PMO may have some criteria, or you may devise your own. They may look something like this (this assumes the plan has been baselined):

  • Red = Item that should be complete by now but isn’t
  • Amber = Item forecast to finish later than baseline finish date
  • Green = Item forecast to finish on baseline finish date or earlier

You need to formulate the tests in such a way that they can be expressed as a mathematical expression that can be tested and return a TRUE or FALSE result. For example, to test whether an item should have finished by now, test for [Forecast finish date] < [Today’s date]. If the answer is TRUE, the item should have finished by now.

These tests can be made as simple or complex as you like, for example you could enhance the Red test so that Red returns true if the item (is forecast to finish earlier than today’s date) OR (is on the Critical path, and is forecast to finish later than baselined) OR (is on the critical path and has not yet been baselined).

After testing for item completion (because if the item is complete then none of the other tests apply) these tests will be applied in order of decreasing severity (because an item that triggers a Red condition would probably trigger all the Amber conditions too).

Implementing the tests

Pick an empty custom task text field (in MS-Project 2016, select Project, Custom Fields), rename it something like “Auto RAG” and click on Formula (see figure 1).

Figure 1: MS-Project custom text formula dialogue box

 

The MS-Project Switch() function applies a number of tests and performs an action for the first test it comes to that returns a TRUE result. The format is:

Switch(
TestForCompleteCondition, ResultIfTestIsTrue (“C”),
TestForRedCondition, ResultIfTestIsTrue (“R”),
TestForAmberCondition, ResultIfTestIsTrue (“A”),
TestForGreenCondition, ResultIfTestIsTrue (“G”),
TestThatAlwaysReturnsTrue, ResultIfTestIsTrue (ErrorMessage),
)

The TestThatAlwaysReturnsTrue at the end “catches” the condition where none of the previous tests return a True result. Seeing the “ERROR!” result should prompt you to check your tests (or add some more conditions to catch other scenarios).

In “coded” format, such a series of tests would look like this:

Switch(
      ([%Complete] = 100), “C”,
      ([Baseline Finish] , Now()), “R”,
      ([Finish] > [Baseline Finish]), “A”,
      ([Finish] <= [Baseline Finish]), “G”,
      True, “ERROR!”
 )

I have formatted this to make it more readable. Be aware that MS-Project will strip out the spaces and returns, so the formula will look a lot less readable if you ever have to come back to edit it.

Put this in the “formula box” (see figure 2).

Entering the Auto RAG formula in the custom text field formula box
Figure 2: Entering the Auto RAG formula in an MS-Project® custom text field formula box

Once you have a text RAG value, you will probably want to display that as a graphical indicator to make reds and ambers easier to pick out in a long plan. This is done from the same Customise Fields dialogue box as before (figure 1), but this time using the Graphical Indicators option, which gets you here:

Specifying graphical indicators for RAG status in an MS-Project® custom text dialogue box
Figure 3: Specifying graphical indicators for RAG status in an MS-Project® custom text dialogue box

The approach is to test whether the value in the field “equals” each of the RAG status values and to assign an appropriate graphical symbol to each.

The What(’s in it for you)

Congratulations! You have now introduced some schedule automation and given MS-Project the ability to tell you what the RAG status of an item should be, based on a rigid interpretation of whatever rules you have given it, ready to have overlaid upon it any other factors the PM knows about. What approaches do you use to calculate RAG status in MS-Project?

This approach comes to you from my experience of getting it wrong and coming up with a better way. If you would like to have the benefits of an approach like this but prefer not to have to worry about this sort of techy stuff, why not talk to us about taking care of it all for you on your next big project or programme?

Copyright © Ken Burrell, Pragmatic PMO Ltd 2017


® Microsoft and Project are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Author: Ken

Ken Burrell is a contract Project, Programme and Portfolio Office (PMO) Professional, who makes targeted improvements to PMO practices to add value to Projects, Programmes and Portfolios through engagements of his company Pragmatic PMO. He provides senior management with the analysis they need to make decisions, and gives project and programme managers the support they need to deliver solutions.

2 thoughts on “Schedule automation: How to customise MS-Project® text fields to calculate RAG Status”

  1. Interesting read Ken. It’s something I’ve used to try and engage policy people in planning. Great success when I changed the RAG ratings to smiley (G) and grumpy (R) faces. No-one wanted a grumpy face!

    1. Hi Lorna, thanks for reading the article and taking the time to comment. I’m glad it struck a chord with you. I have also used faces to indicate RAG status, mainly to overcome potential issues with red/green colour blindness (more common than you’d think) and monochrome printing / photocopying (prohibitively expensive a few years ago, less of a problem nowadays). Things like emoticon faces get a somewhat mixed reaction in corporate circles (e.g. financial services). I think it’s a friendly and visual (especially if the faces are also coloured Red 🙁 / Amber 😐 / Green 🙂 ) way to give a quick indication of how things are going. And you’re right: no-one wants to get a grumpy face!

What do you think? Leave a Reply to let us know.