Microsoft Project can be configured to display graphical status indicators which provide a quick view of certain data. This can allow Executives, Project Managers, and Team Members to easily manage projects on an exception basis, rather than unnecessarily spending large amounts of time analyzing detailed project data.
The following technique displays a column of Graphical Indicators in your MS Project schedule which provides a quick view of how the tasks are tracking against the baselined schedule. The technique utilizes MS Project's ability to use custom fields, formulas, and graphical indicators.
The Schedule Status Indicator Explained
When the Schedule Status is evaluated for each task, the task is tested based on a set of criteria:
1. Is the task complete?
2. Is the task overdue?
3. Has the task been baselined?
4. If the task is scheduled into the future, are we forecasting the task to finish on time?
1. Is the Task Complete?
If a task has been completed, then we no longer have the ability to leverage that task to make future schedule improvements.
A completed task is indicated with the following graphical indicator:
(Complete)
2. Is the Task Overdue?
If we've passed the scheduled finish date for a task and it is still incomplete, then the task is overdue, and we no longer have the ability to leverage that task to make future schedule improvements. In fact, it is probably forcing downstream tasks to run late, so it's probably best to finish the work ASAP.
An overdue task is indicated with the following graphical indicator:
(Overdue)
3. Has the Task Been Baselined?
In order to forecast whether or not tasks will finish on time in a dynamic project schedule, the Project Manager should set a baseline when the schedule is initially created and accepted; this baseline is used throughout the duration of the project for variance analysis. Without a baseline, we cannot accurately and automatically measure variances in the scheduling of tasks.
A task which does not have a saved baseline is indicated with the following graphical indicator:
(No Baseline)
4. Will Future Tasks Finish On Time?
As tasks are executed, schedule variances often occur (items finish earlier or later than originally planned) which affect the finish dates of other tasks in the future. If the Project Manager has set a baseline, then we can then use this baseline to forecast whether tasks will finish early or late.
A task which is forecasted to finish on-time or early is indicated with the following graphical indicator:
(Green)
A task which is forecasted to finish less than 10% late (based on the overall duration of the project) is indicated with the following graphical indicator:
(Yellow)
A task which is forecasted to finish more than 10% late (based on the overall duration of the project) is indicated with the following graphical indicator:
(Red)
Setting Up the Schedule Status Indicator
The formula, as shown below, is the basis for calculating which of the graphical indicators will be displayed in the project schedule. It is intended to be used with a custom text field, and it tests for these six conditions:
- The task is complete
- The task is overdue (finish date has passed, and task is incomplete)
- The task has no baseline
- The task is on time or early
- The task is less than or equal to 10% late (based on the project duration)
- The task is more than 10% late (based on the project duration)
Here is the formula:
Switch(
[% Complete]=100,"Complete",
(([% Complete]<100) And ([Finish]<Date())),"Overdue",
(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",
[Finish Variance]<=0,"Green",
[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",
[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red"
)
To configure the Schedule Status Indicator in MS Project, follow these steps:
1. Open the Gantt Chart (or similar) view in MS Project, select one of the columns in the left-hand task table, then select Insert > Column:
2. In the Column Definition dialog box, select an unused text field from the Field name drop-down list, (such as Text1), then click the OK button:
An empty column labeled Text1 will appear in the task table:
3. Select Tools > Customize > Fields to customize the Text1 field:
4. In the Custom Fields dialog box, select the Task field type at the top of the dialog, select Text from the Type drop-down list in the upper right corner of the dialog, select the Text1 field from the Field list, then click the Rename button:
5. In the Rename Field dialog box, enter a name for the custom field (such as Schedule Status), then click the OK button:
6. After returning to the Custom Fields dialog box, click the Formula button, located below the Custom attributes heading:
7. In the Formula dialog box, type or paste the formula supplied earlier, then click the OK button:
An alert message will appear, communicating that any existing data in the custom field will be overwritten; simply click the OK button to acknowledge the message:
8. After returning to the Custom Fields dialog box, select the Use formula option below the Calculation for task and group summary rows heading, select the Roll down unless manually entered option below the Calculation for assignment rows heading, then click the OK button:
The newly-inserted Text1 column will now bear the Schedule Status heading, and calculated status values will appear in the column; when the formula finds one of the six conditions to be true (as indicated previously), it displays one of the following results in the custom text field:
- Complete
- Overdue
- No BL
- Green
- Yellow
- Red
9. To add graphical indicators to the Schedule Status field, return to the Custom Fields dialog box (Tools > Customize > Fields), select the new field, and click the Graphical Indicators button, located under the Values to display heading:
10. In the Graphical Indicators dialog box, enter six conditional tests, as well as their respective images:
Test for | Value(s) | Image |
Equals | Complete | 
|
Equals | Overdue | 
|
Equals | No BL | 
|
Equals | Green | 
|
Equals | Yellow | 
|
Equals | Red | 
|
11. Select the Summary rows option, then select the Summary rows inherit criteria from non-summary rows check box:
An alert message will appear, communicating that the graphical indicator test criteria will be inherited; simply click the Yes button to acknowledge the message:
12. Select the Project Summary option, then select the Project Summary inherits criteria from summary rows check box:
An alert message will appear, communicating that the graphical indicator test criteria will be inherited; simply click the Yes button to acknowledge the message:
13. Click the OK button to close the Graphical Indicators dialog box, then click the OK button to close the Custom Fields dialog box.
Graphical Indicators will appear in the Schedule Status column, replacing the text values which appeared previously:
The criteria which are used in this example (in the formula and the graphical indicators) may not match the business rules in your company, but hopefully they will help you to understand how the custom fields, formulas, and graphical indicators in Microsoft Project can be used to create useful reporting views for better decision making.
Good luck!