I recently found an excellent tutorial on project indicators from Ira Brown, President and CEO of Project Widgets, and the Microsoft Project Users Group (MPUG) YouTube channel. In the video below, Mr. Brown demonstrates how to create an indicator that identifies tasks that should have started or finished based on the current date.
I really like this tutorial because it creates a red flag indicator that is only removed if you’ve completed the task or adjusted the Finish date. The only challenge I had with the video tutorial was trying to decipher the formula. If you expand the video to full screen, you can squint and decipher the formula. Rather than straining your eyesight, the original formula is:
IIf((Date()>[Start] And [% Complete]=0) Or (Date()>[Finish] And [% Complete]<>100),True,False)
In the video, the recommendation is to use the current system date to compare the task start or task finish dates. When I evaluate project status, I typically view it from the project status date and not the current date; this allows project teams to provide updates to only the specific tasks that are due as of the project status date. In lieu of my own video, here are steps to recreate the indicator flag using the project status date.
Step 1: Using the Gantt Chart view, insert the Flag1 column.
Step 2: Right-click the Flag1 column and select Custom fields.
Step 3: Click the Rename button and call it Update Required (Figure A).
Rename the Flag1 field
Step 4: Click the Formula button and paste in this formula (Figure B):
IIf(([Status Date]>[Start] And [% Complete]=0) Or ([Status Date]>[Finish] And [% Complete]<>100),True,False)
If you are familiar with comparing fields in Excel, then the IFF function is the standard IF-THEN clause. In this example, I use the Status Date variable, which is specific to the date the project manager sets for the project status date. By clicking the Field button, you can select from a variety of fields available in Microsoft Project.
Step 5: Change the Graphical Indicator to display a red flag (or whatever image you prefer by selecting the appropriate icon from the list) if the value equals Yes (Figure C).
Step 6: Click the OK button and set the project status date by selecting the Project tab and clicking the Status Date calendar icon (Figure D).
Project schedule with indicators
In Figure D, I created three simple tasks. Task 2 is Task Not Started, which has a date range of 9/3/2012 - 9/3/2012 and the percent complete is 0%; in this case, the first condition is satisfied and the flag is set. Task 3 has been started and the finish date is past the project status date so the task is okay and no indicator is set. Task 4 is an active task with a Finish date beyond the project status date and the percent complete is not 100%; this satisfies the second IF condition and the flag is set.
In this example, I demonstrated how to customize Microsoft Project 2010 to use simple indicator flags. You can apply these formulas to baseline dates, cost fields, and a variety of other Microsoft Project fields. I encourage you to use this tutorial as a basic framework for creating your indicators. I also recommend visiting the MPUG YouTube channel and checking out their tutorials.