Part of a series on customising 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 diagnose schedule management problems 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 diagnose schedule management problems by explaining 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 to diagnose schedule management problems. Then you will 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 to diagnose schedule management problems by applying a series of simple tests and generating text snippets. 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:
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 <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:
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 quickly and simply to diagnose schedule management problems and 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 to diagnose schedule management problems 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 would prefer not to get bogged down in the detail, The PMO Professionals can help. Why not take a look at our “Savvy scheduling” service, and if that looks interesting, get in touch to discuss how we can help you?
® Microsoft and Project are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
