Last fall (October 2015), I was working with a client who wanted to provide the typical red, yellow, green "stop light" indicator for tasks that were essentially behind schedule as a quick visual for Project Managers. This then would rollup to the project level to be factored into the overall project schedule "health" calculation. There were many discussions around what "behind schedule" meant for them because there are many ways and different factors that can determine if a task is considered behind schedule as well as what threshold did the business what to consider a task for preventative or corrective action. Out of those, we decided to focus on a metric that we called "Expected % Complete". Because they would be using Percent Complete to record progress on task, this consisted of calculating the percentage complete that the task should be at a given date if all work was progressing normally. For example, if you have a task that has a duration of ten (10) days, on day five (5) the task should be expected to be 50% complete. If the task is actually only 20% complete at this halfway mark then the indicator might show a red stoplight. If the task was actually 70% complete, then the indicator would likely show as a green stoplight. We also had quite a number of discussions debating the use of baseline Start and Finish dates (Baseline Start / Baseline Finish) over what are essentially the estimated Start and Finish dates, as well as whether to use the Project Status Date or the current date. Which is right for you and your company may be different, but for purpose of this post we will be targeting the estimated Start and Finish dates as well as the current system date. You should be able to use the content in the rest of this post to modify the formulas and values for what is right for your particular situation. Let's setup the scenario. First, we are assuming that % Complete is being used to record progress against tasks. Second, we will be assuming that all tasks are duration based tasks. If you are using another task type, for example fixed units or fixed work tasks, then you will need to adjust the formula accordingly. Third, we will be using the current system date to calculate the Expected % Complete and not the Project Status Date. Lastly, I'm assuming that you already know how to create either local custom fields within your project in Microsoft Project or know how to create Enterprise Custom Fields within Project Server or Project Online. Also, I used a text custom field to hold this formula because the client wanted the percent complete formatted as a percentage strictly for visual purposes. This actually made creating the indicator field harder because I had to convert it back to a number before evaluating it against the red, yellow and green thresholds.
Now the formula:
Don't get freaked out here. It's not as bad as you might think. A lot of this is testing certain conditions that might occur for a task such as whether the duration is zero like for a milestone task ([Duration]=0), the duration is only estimated ([Estimated]="Yes"), and whether the task's finish date has already passed ([Finish]<Now()). Let's break it down, okay?
First, the heart of the calculation is this: (ProjDateDiff([Start],Now())/[Duration])). Basically this says subtract the current date from the task's Start date field and divide by the duration of the task. For this we use to functions, the Now() function to represent the current date and the ProjDateDiff function to subtract the dates. For those familiar with VBA, you might be wondering why we are not using the DateDiff function. The ProjDateDiff is a special Project only function. It's special because it takes into account the calendar settings and hours per day that might be set for a particular task. Why is that important? Remember that tasks can use either the calendar that is for the overall project or a specific task calendar. Additionally, if you have resources assigned to that task (and you absolutely should) then they can each have individual calendars associated with them as well. Those will define that resources work day, hours and such. So, let's take a simple example. Let's say that you have a task that has a calendar that defines a work day as 10 hours/day and another task that has a calendar that defines a work day as 8 hours /day. Rather than having to figure out how the day unit is define for that task, and add those consideration to your formula, you can use the ProjDateDiff function to do all the heavy lifting for you. Additionally, duration can be a particularly vexing field to use in calculations because the value that comes back from referencing a duration type field is represented in minutes, not hours or days. There is also a really great constant that is available that can be used called [MinutesPerDay] that takes into account the calendar et al. Here I'm not using it because both the result of the ProjDateDiff function and the Duration are in minutes already. I don't need to convert them to anything else to get my Expected % Complete.
The second thing that might be throwing you is the IIf parts of this formula. This is known as an Immediate IF function. Basically it says, if the first part is true do this, other wise to that. It's format is like this: IIf(expr, truepart, falsepart). Because I'm testing for different conditions that might exist in the task, I have several nested IIf functions in the formula where the false part of the IIf function is another IIf function. First, I test to see if the duration is zero. If it is then the percentage is zero. Next, if the duration is still set as an estimated value, either because it still has the ? or is set to Estimated, this can do weird things to the duration value so I want to nip those in the bud. Lastly, I check to see if the Finish date is before the current date because if it is no matter how long ago this task should have been finish it should be 100%. Wrap all this in a Format function to format the result as a percentage and you are done.
So let's see this in action. Below is a project schedule with the Expected % Complete value custom field with a system date is 01/24/2016. I've filled in several percentage completes and also exposed several additional fields for you to peek at. Now all that's left is to create an indicator field based on my Expected % Complete value and the red, yellow and green thresholds.
Links: Project functions for Custom Fields: http://bit.ly/25dFPAd
email Christine: firstname.lastname@example.org