Planners' Place

View Original

Display summary task percent complete when all subtasks are milestones in Microsoft Project

When all subtasks in Microsoft Project are milestones, the summary task percent complete is always 0% even when some of these milestones are completed unless all the subtasks are 100%.

The workaround in this tutorial will display a % complete based on number of milestones completed. For example, if all 5 subtasks are milestones and 2 are 100% complete, our workaround will display 40% for the summary task instead of the 0% that Microsoft Project will display.

We need to create 5 custom fields, 1 text field and 4 number fields, which will serve as helper columns.

Helper Column No.1

  1. Add a custom field using Number1 or any other Number field and rename it “No of Milestones”. The purpose of this field is to count the number of subtasks that are milestones

  2. Enter the formula, IIf ([Milestone], 1, 0)

  3. For “calculation for task and group summary rows”, tick rollup and select Sum from the dropdown

Helper Column No.2

  1. Add a custom field using Number2 or any other Number field and rename it “No of Subtasks”. The purpose of this field is to count the total number of subtasks

  2. Enter the formula, IIf (Not [Summary], 1, 0)

  3. For “calculation for task and group summary rows”, tick rollup and select Sum from the dropdown

Helper Column No.3

  1. Add a custom field using Number3 or any other Number field and rename it “All Subtasks are Milestones”. The purpose of this field is check if all subtasks are milestones. So if formula result is 1, then all subtasks are milestones but if 0, not all subtasks are milestones

  2. Enter the formula, IIf ([Summary], [No of Milestones] / [No.of Subtasks] ,0)

  3. For “calculation for task and group summary rows”, tick use formula

Helper Column No.4

  1. Add a custom field using Number4 or any other Number field and rename it “Completed Milestones”. The purpose of this field is to count the number of subtasks that milestones and are 100% complete

  2. Enter the formula, IIf ([Milestone] And [% Complete] = 100, 1, 0)

  3. For “calculation for task and group summary rows”, tick rollup and select Sum from the dropdown

Task %

  1. Add a custom field using Text1 or any other Text field and rename it “Task %”. This field will show the same value as the default Microsoft Project % complete and will also show % complete for summary tasks where all subtasks are milestones and this includes nested summaries

  2. Enter the formula, IIf ([Summary] And [All Subtasks are Milestones] = 1, Format ([Completed Milestones] / [No of Milestones] , "Percent"),[% Complete] & "%")

  3. For “calculation for task and group summary rows”, tick use formula

Good to go

You can now hide all the 4 helper columns and the default % complete column. Your schedule should show a % complete value in the Task % column, for summary tasks where all subtasks are milestones and not all are 100% complete.

See this content in the original post

Important!!!

This workaround will only work if all subtasks of a summary task are milestones, e.g. in a “key dates” or “key milestones” section. Where subtasks are the usual mix of tasks and milestones, the default Microsoft Project rule for calculating progress, where the milestones do not contribute to % complete, applies

Example of default summary % complete when all subtasks are milestones and not all are 100% complete

Example of custom summary % complete when all subtasks are milestones and not all are 100% complete

Addendum 30-Mar-22

This addendum is in response to Miles’ comment.

To add the calculated percent complete to the Gantt chart, Go to Bar Styles (under Gantt Chart Tools) and for Task & Milestone bar styles, click on the Text tab at the bottom of the Bar Styles dialog. Add the calculated percent complete field to either Left, Right or Inside of the bar as shown below (all custom fields are available in the drop-down box).

In this example, I added the calculated percent complete to the right side of the bar and the result is shown below.

Addendum 15-Feb-24

This addendum is in response to Jorien’s comment.

Add a new Number custom field and name it Summary % Factor and use the formula shown in the image below, ensuring that the radio button for summary rows calculation is ticked.

Add a new Finish custom field and name it Summary % Finish and use the formula shown in the image below, ensuring that the radio button for summary rows calculation is ticked.

Under Bar Styles, define a new style just under progress and assign the setting underlined in red in the image below.

The summary bar on the Gantt chart should now reflect a realistic progress bar where all subtasks are milestones as shown in image below.

See this content in the original post