A simple but effective “Update Status Indicator” can be generated using the excellent Custom Fields features available in Microsoft Project 2000 and later versions.
When you need to revise a large project plan, how do you identify those tasks that need updating? By constructing the custom field I describe below, you’ll get red, blue and green indicators that automatically appear against tasks. They indicate, respectively, tasks that should have been completed, that should have been started and that are in progress.
To generate this custom field:
1. Select Tools/Customize/Fields from the menu.
2. In the Customize Fields dialog box select field Type “Number” and the select a Number field from the list (I used Number20).
3. Click the Rename… button and enter “Update Status” to rename the field.
4. A warning appears that any existing data in the Update Status field will be deleted. Click OK.
5. Click the Formula button and enter the following formula in the Formula for Update Status dialog box:
IIf(([Finish]<=[Status Date] And [% Complete]<100),3,
IIf(([Start]<=[Status Date] And [% Complete]=0),2,
IIf(([Finish]>[Status Date] And [% Complete]>0),1,
6. Click OK.
7. A warning appears that any existing data in the Update Status field will be deleted. Click OK.
8. Under “Calculation for task and group summary rows” click Use Formula.
9. Click the Graphical Indicators button.
10. In the Graphical Indicators for Update Status dialog box enter the following:
Test “equals” 1.00, Image green button
Test “equals” 2.00, Image blue button
Test “equals” 1.00, Image red button
11. Click OK, then click OK in the Customize Fields dialog box.
12. The customized “Update Status” field is now ready and can be included in the table for your view. Enter the Status Date in the Project Information dialog box (Project/Project Information…).
Here’s how the customized field works. The formula first checks to see whether the task is a summary task, a completed task or has a Start date later than the Status Date in the Project Information dialog box. If so, the formula returns a value of 0 in the custom field. If not, it checks to see if the Finish date is earlier than the Status Date but not 100% complete. If it is complete, the formula returns a value of 3. If not, the formula checks to see if the Start date is before the Status Date but is still 0% complete. If so, a value of 2 is returned. If not, the formula checks for tasks started but not due to finish until after the Status Date, in which case a value of 1 is returned. The values 1, 2 and 3 are then displayed as the appropriate green, blue and red indicators