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.
Field | Rename | Formula | Summary |
Number1 | Number of MS | IIf([Milestone],1,0) | Rollup as Sum |
Number2 | Completed MS
| IIf([Milestone] And [% Complete]=100,1,0) | Rollup as Sum |
Number3 | MS % Complete | IIf([Summary]=Yes And [Flag1]=No,([Number2]/[Number1])*100,[% Complete]) | Use Formula |
Text1 | % Done | IIf([Summary]=Yes And [Flag1]=No,[Number3],[% Complete]) & "%" | Use Formula |
Flag1 | Inc Tasks? | IIf([Summary]=No And [Milestone]=No,"yes","no") | Rollup as OR |
Finish1 | MS Complete Through | IIf([Summary]=Yes And [Flag1]=No,ProjDateAdd([Start],([Duration]*[Number3]/100)),"NA") | 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:
Field | Formula | Summary |
Number1 | IIf([Milestone],1,-1000000) | Rollup as Sum |
Number2 | IIf([Milestone]=Yes And [% Complete]=100,1,0) | Rollup as Sum |
Text1 | IIf([Summary] And [Number1]>0,([Number2]/[Number1])*100,[% Complete]) & "%" | use formula |
Comments