Schedule automation: How to customise MS-Project® text fields to show schedule management diagnostics

Part 3 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. You’ve set up fields to calculate RAG status, but what if you want some schedule management information to go with it, in the form of some compact RAG status explanation to go with it, without having to study the baseline vs. forecast dates and % complete, working it out in your head line by line?

This post describes how to customise a Text field to display a very compact schedule management commentary to explain why a project schedule item should be Red or Amber.

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

In a separate post I have explained why it can be useful to calculate RAG status of projects tasks based on scheduling criteria alone. As well as having the RAG status, it is also helpful for schedule management to know why the task is red or amber, to help you to identify appropriate corrective action. Setting up a field to display some commentary on the status of tasks enables you to interpret the calculated RAG status, show or hide the commentary as appropriate, and use the commentary to prompt discussion with the project manager.

The How

Like the approach I have presented for calculating a RAG status, this approach uses custom fields and formulae generates text snippets by applying a series of simple tests. This time the test compare dates and % complete, using text concatenation to join the snippets together to form a very compact commentary.

To do it, first pick an empty custom task text field (in MS-Project 2016, select Project, Custom Fields), rename it something like “Auto commentary” 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 returns a result for the first one it comes to that is true. Devise and apply some tests in order of decreasing severity (because it’s more important to test whether a task has missed its baseline due date for example than it is to test whether it’s slipping from its future baseline date).

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

Switch(
      ([% complete] = 100), “C”,
      ([Baseline Finish] <> projDateValue(“NA”)) AND
          ([Baseline Finish] < Now()), “BL FL”,
      ([Baseline Finish] <> projDateValue(“NA”)) AND
          (([Baseline Finish] <= DateAdd(“d”,28,Date())) OR
          ([Baseline start] <= DateAdd(“d”,28,Date()))), “BL S or F<4w”,
      ([Baseline Finish] = projDateValue(“NA”)) AND
          ([Finish] < Now()), “UB FL”,
      ([Baseline Finish] = projDateValue(“NA”)) AND
          (([Finish] <= DateAdd(“d”,28,Date())) OR
          ([Start] <= DateAdd(“d”,28,Date()))), “UB S or F<4w”,
      True, “ERROR!”
 )

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

Entering the Auto commentary formula in the custom text field formula box
Figure 2: Entering the Auto commentary formula in the custom text field formula box

This example uses well-documented “tricks” such as:

  • [Baseline Finish] <> projDateValue(“NA”) ==> Item is baselined
  • [Baseline Finish] = projDateValue(“NA”) ==> Item is not baselined
  • [Baseline Finish] < Now() ==> Baseline Finish is in the past
  • [Baseline Finish] <= DateAdd(“d”,28,Date()) ==> Baseline Finish is within the next 28 days (although with a bit of thought you could make this formula use a field for the size of the date “window”)

The True statement 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 to catch other scenarios). You can devise tests to detect conditions of interest to you. It would make sense if these are related to the tests you apply to calculate RAG status.

The output displays abbreviated commentary such as this:

  • BL FL ==> BaseLined item, forecast to Finish Late
  • BL S or F <4w ==> BaseLined item, Start OR Finish within 4wks of now
  • UB FL ==> UnBaselined item, Finish Late
  • UB S or F <4w ==> Unbaselined item, Start or F within 4wks of now

The What(’s in it for you)

With a little bit of code, this enables MS-Project to quickly and simply show you why an item has the RAG status it does, without the need to apply multiple filters. I find this a really helpful schedule management aid, to prompt discussion when reviewing schedules with project managers. There are probably limits for the number of Switch() function tests or characters in the Formula box, but I have yet to discover what they are (maybe one of you knows?).

Is this approach useful to you? What do you use custom fields for? What could you use them for?

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 a schedule management 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.

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