SharePoint (and Project Server) Shenanigans

One query that we receive a lot from our clients is about setting up automatic traffic light (or RAG) indicators for their project schedule. This is a very well documented request, but a recent client wanted a slight variation – the project schedule indicator to have its tolerance based on a percentage, instead of a hard-coded value. The following formula solves this issue:

 

IIf([Baseline Duration]=0,"No Baseline",

Switch([Baseline Start]=ProjDateValue(‘NA’) Or [Baseline Finish]=ProjDateValue(‘NA’),"No baseline",

[Duration Variance]<=0,"On schedule",

[Duration]/[Minutes per day]>([Tolerance for schedule in percent]/100)+([Baseline Duration]/[Minutes Per Day]),"Outside tolerance",

[Duration]/[Minutes per day]<=([Tolerance for schedule in percent]/100)+([Baseline Duration]/[Minutes Per Day]),"Within tolerance"

))

This is for the task schedule RAG, and will return values based on a number field called “Tolerance for schedule in Percent” to indicate whether the task duration has increased beyond its allowed tolerance.

You will also need to set up RAG graphical indicators for this field as well, with the following values, as per the screenshot below:

No baseline = Question Mark

On schedule = Green

Within tolerance = Amber

Outside tolerance = Red

Of course, your indicators could be different to those that I have chosen, as well as the text. Just make sure to update the formula if you want to change the returned text.

Hopefully this will shorten your chin-scratching time when attempting to do something similar!

Lester