A summary task containing only milestones as sub-tasks will only show 0% or 100% complete. This can be frustrating as it doesn’t reflect the progress made.
It would be better to be able to report the progress as a % of the milestones under the summary which have been completed. Even better would be showing this progress on the Gantt chart.
We need to use several Custom Fields; see below. It is possible to do the % complete aspect with fewer fields, as shown at the end.
- Number1 – Count up the milestones which make up the summary
- Number2 – Count up how many milestones have been completed
- Number3 – Calculate the % complete for relevant summary tasks.
- Text1 – Display the % complete shown in Number3 OR the standard % complete if the summary task contains any tasks
- Flag1 – Identify if the summary task contains any tasks, in which case we have to use the standard % complete. It also allows us to manipulate the Gantt chart view
- Finish1 – Calculate the “complete through” value for the summary tasks made up of milestones.
To add the custom field calculations to your plan right mouse click on the column headers and choose Custom Fields
Select the field you want to operate on, rename it and enter the required formula.
Number1
Rename: Number of MS
Formula: IIf([Milestone],1,0)
Summary: Rollup as Sum
Number2
Rename: Completed MS
Formula: IIf([Milestone] And [% Complete]=100,1,0)
Summary: Rollup as Sum
Number3
Rename: MS % Complete
Formula: IIf([Summary]=Yes And [Flag1]=No,([Number2]/[Number1])*100,[% Complete])
Summary: Use Formula
Text1
Rename: % Done
Formula: IIf([Summary]=Yes And [Flag1]=No,[Number3],[% Complete]) & "%"
Summary: Use Formula
Flag1
Rename: Inc Tasks?
Formula: IIf([Summary]=No And [Milestone]=No,"yes","no")
Summary: Rollup as OR
Finish 1
Rename: MS Complete Through
Forumula: IIf([Summary]=Yes And [Flag1]=No,ProjDateAdd([Start],([Duration]*[Number3]/100)),"NA")
Summary: Use Formula
Now you can add the new % done (Number3) column to your Gantt chart or reports. Note that as this is a completed field, you will not be able to change it. You may want to keep the standard % Complete field available to update tasks.
To display the progress bar on the Gantt chart
- Double click on a blank space to bring up the bar Styles dialogue.
- After the existing summary bar style, insert two spaces and name the two new styles as shown.
- Apply the formatting you want to see for each style. In this example, we have different colours to show that one summary is the standard MSP % complete and the other is our calculation.
An excellent blog by Jerome Odeh inspired this blog. I have done things a little differently; this is the joy of MS Project – there are generally at least two ways to do something.
If you don’t need to display on the Gantt chart you could reduce the number of fields used by:
Number1
Formula: IIf([Milestone],1,-1000000)
Summary: Rollup as Sum
Number2
Formula: IIf([Milestone]=Yes And [% Complete]=100,1,0)
Summary: Rollup as Sum
Text1
Formula: IIf([Summary] And [Number1]>0,([Number2]/[Number1])*100,[% Complete]) & "%"
Summary: use formula