Have you ever seen graphical status indicators in Microsoft Project 2010 and wondered how to create them? Today’s post will take us through some basic steps to create the custom field for the graphical indicators from last week’s post.
As a refresher, here’s the custom view we worked with last time: (Note: Click on graphics to view large images).
As you’ll see, the status has changed. Before we look at those changes, let’s go through the steps you’ll take to create this custom graphical indicators field in your MS Project 2010 schedule. First, insert the % Complete column and an unused Number field (I used Number1) as shown below:
Next, right click on the Number1 column and select “Custom Fields” and the Custom Fields dialog box will appear. Then select the Formula button and enter the formula shown in the Formula dialog box (and below):
IIf([Finish]<[Status Date],”100″,IIf([Start]<[Status Date] And [Finish]>=[Status Date],Round((ProjdateDiff(Start,[Status Date])/[Duration])*100,0),0))
Finally, rename this field “% Should Be Complete.”
Are you wondering what this Formula is for and why we inserted the % Complete field and then created this new % Should Be Complete field? The new % Should Be Complete custom field calculates the percent of the duration that should be complete on the status date. You’ll see how this field will be used in the formula for the graphical indicators.
Insert an unused Text field next (I used Text20). Right click on the column header and select Rename from the Custom Fields dialog box. Rename the field, “Graphical Indicators,” or another name of your choosing.
Next, select Formula and enter the code as shown above (and below):
Switch([% Complete]=100,”Complete”,[Finish]<Date(),”Overdue”,[% Complete]=0 And [Start]<Date(),”Late Start”,[Start]>Date() And [Start]<NOW()+15,”Starts Soon”,[% Complete]>=[Number1],”On Track”,[% Complete]<[Number1],”Needs Update”)
This formula returns a value as soon as it recognizes a true statement, so the order is important. First, we mark any task that has been finished as “Complete.” Next any incomplete task with a finish in the past is marked “Overdue.” So far, we’ve “picked the low-hanging fruit.”
Next, any task that has a start date in the past and does not show any progress is marked as a “Late Start.” Tasks starting within the next 15 days are identified by “Starts Soon.” That 15 days can be modified to suit a particular project time span.
The remaining tasks are open tasks with progress marked against them. But is the progress on track? If it is on track, % Complete will be equal to or greater than the % Should be Complete field we created earlier. Those tasks will be marked “On Track.” If % Complete is less than % Should be Complete, the task is marked “Needs Update.”
We don’t know whether the tasks marked Needs Update are behind or if they simply need to be updated. Either way, a discussion with the owner of that task is in order.
It may not be obvious, but when there is incomplete work to the “left” of the status date, (i.e., scheduled earlier than the status date), the entire schedule is invalid until that situation is corrected. Why? Because you can’t go back in time and perform that unfinished work that is sitting in the past. That unfinished work represents a hidden slip in the entire remaining schedule.
You’ve probably seen schedules that had guesstimated % Complete values entered directly, and where unfinished work sitting in the past was used as an indicator that the task owners needed to “make up for lost time.”
As many who have learned this the hard way would agree, it’s not a good idea to make guesstimates of % Complete and it is not a good practice to have any unfinished work scheduled in the past. It is far better to record the verifiable work has been completed and then reschedule unfinished work into the future when it can be completed. A new estimate of work remaining may be required.
If you modify this formula, or create your own, I’d recommend that you create a logic tree to make sure there are no “gaps” in logic that will return an error. We won’t go into any more detail here on how to verify and validate the formula, but contact me or leave a comment if you’d like to see an example of how to do that. Most formulas we’ve worked with needed some tweaking once we started using them.
Our final step will be to enable and define the graphical indicators. Select the Graphical Indicators button in the Custom Fields dialog box as shown below:
Next, enter the exact text for each result returned by the formula and select the desired graphical indicator from the drop down menu. The next two figures show the indicators defined for this example:
Note that if the text for the graphical indicators is not exactly as it appears in the formula, the indicator will not appear.
Now close the windows and go back to our original custom view. You can hide the % Complete columns if you haven’t done that already. Here’s what the current schedule looks like:
The current date is today, 2/18/13, and is indicated by the red dashed line. The status date is this past Friday, 2/15/13, and is indicated by the blue dashed line. You can see that we have completed several tasks since last week, but we have issues with several other tasks. Filtering on each of the results is one way to zero in on specific types of tasks during a status review.
The meeting might start by filtering on the green check marks (Complete) and celebrating a job well done. Next, we could deal with Activity 1-4 (Overdue) and Activity 1-5 (Late Start) and reschedule the unfinished work. That would allow us to see a more realistic plan for Activity 1-6, which is marked “Starts Soon.”
Next we could look at the activities marked “Needs Update” to find out why their progress is lagging. Activity 2-4 should be 40% complete and it is only 10% complete. Review/Approve Test Plan is 70% complete and should be 80% complete. We can update the schedule with status from the Software Engineer and the Test Engineer.
This example showed how you can create graphical indicators that can be used for various levels of communication, visibility, and reporting. We used a variety of indicators and a relatively complex formula. It’s easy to scale this example down to make it simpler to suit a particular need.
Next week I’m thinking about covering how to create a product-oriented WBS and then create a schedule from that WBS. Comments and questions are welcome as well as requests for other topics.