% Complete using Target Hours

Tedd Powers

How do I get the % Complete column to reflect the percent of Target Hours complete rather than the duration of days?

jmyzk_cloudsmart_jp

Hi @Tedd Powers

First, I interpreted the "number of hours completed against goal" as follows;

• number of hours completed = [Target Hours]@row * [% Complete]@row
  • For example, if the target hour is 4 hours and the % Complete is 50%, the "number of hours completed" is 2 hours.
• number of hours completed against goal = number of hours completed / the parent's target hour (or sum of children's target hour)
  • Then, if the parent's target hour is 24 hours, the "number of hours completed against goal" is 2/24 = 8%. (row 13, Duration % Children)

Second, I analyzed how Smartsheet calculates the % Complete when Dependencies are set as "Dependencies enabled".

• As shown in the image of the demo sheet below, Smartsheet calculates % Completed in parents' rows with the weighted average.
  • The calculation is a weighted average of the % Complete by the ratio of the duration of individual tasks to the total duration of the children's tasks.
• So, first, I added a helper column, [Duration SUM], which adds children's duration. (Note: Duration in the parent's row is not the sum of the children but the duration between the oldest start date and the newest end date. For example, at the Primary=B row, Duration i1 9d but sum of children is 4)
  • [Duration SUM]@row= =IF([is Parent]@row, SUM(CHILDREN(Duration@row)))
• Then, I added the [Duration % Children] helper column, which calculates the following:
  • [Duration % Children]=IF(Anc@row > 0, Duration@row / PARENT([Duration SUM]@row))
    • Anc is a helper column to calculate the number of ancestors. (The top grandparent row gets 0.)
• Finally, [% Complete by Formula] sums the children of [Duration % Children] to get the same results as Smartsheet's [% Completed] at the parent row.
  • [% Complete by Formula] =IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Duration % Children]@row))
    • "IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row" part does the same calculation for the grandparent row.

Lastly, I applied the same logic to calculate [% Hour Complete].

• [Target Hours SUM]=IF([is Parent]@row, SUM(CHILDREN([Target Hours]@row)))
• [Hour % Children]==IF(Anc@row = 0, SUM(CHILDREN([Hour % Parent]@row)), [Target Hours]@row / PARENT([Target Hours SUM]@row))
• [% Hour Complete]=IF(Anc@row = 0, SUM(CHILDREN([Hour % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Hour % Children]@row))

It would have been easier if I calculated the simple average of the [% Complete] or [% Hour Completed]. Still, Smartsheet's weighted average approach gives me a more accurate completion rate, considering each completed child's relative length.

https://app.smartsheet.com/b/publish?EQBCT=ce03d423be264626919ca34a30a96505

jmyzk_cloudsmart_jp

@Tedd Powers

Is the "Target Hours" something different from the duration of hours?

In the example below, Parent 1 has 20 hours of work. Out of these, 50% of 8 hours is not complete, so 16 out of 20 hours are complete, so the parent %Complete shows 80%.

https://app.smartsheet.com/b/publish?EQBCT=aa403466516147d3a55e972179db0364

Tedd Powers

My structure is such that I have a Target Start Date, a Target End Date, and a Target Amount of Hours. As the project progress, I would like to track the actual start/end dates and the actual hours. I want the Complete % field to be based on the number of hours completed vs target as opposed to the number of days completed. I have included the project settings screen shot below the workplan.

jmyzk_cloudsmart_jp

Hi @Tedd Powers

First, I interpreted the "number of hours completed against goal" as follows;

• number of hours completed = [Target Hours]@row * [% Complete]@row
  • For example, if the target hour is 4 hours and the % Complete is 50%, the "number of hours completed" is 2 hours.
• number of hours completed against goal = number of hours completed / the parent's target hour (or sum of children's target hour)
  • Then, if the parent's target hour is 24 hours, the "number of hours completed against goal" is 2/24 = 8%. (row 13, Duration % Children)

Second, I analyzed how Smartsheet calculates the % Complete when Dependencies are set as "Dependencies enabled".

• As shown in the image of the demo sheet below, Smartsheet calculates % Completed in parents' rows with the weighted average.
  • The calculation is a weighted average of the % Complete by the ratio of the duration of individual tasks to the total duration of the children's tasks.
• So, first, I added a helper column, [Duration SUM], which adds children's duration. (Note: Duration in the parent's row is not the sum of the children but the duration between the oldest start date and the newest end date. For example, at the Primary=B row, Duration i1 9d but sum of children is 4)
  • [Duration SUM]@row= =IF([is Parent]@row, SUM(CHILDREN(Duration@row)))
• Then, I added the [Duration % Children] helper column, which calculates the following:
  • [Duration % Children]=IF(Anc@row > 0, Duration@row / PARENT([Duration SUM]@row))
    • Anc is a helper column to calculate the number of ancestors. (The top grandparent row gets 0.)
• Finally, [% Complete by Formula] sums the children of [Duration % Children] to get the same results as Smartsheet's [% Completed] at the parent row.
  • [% Complete by Formula] =IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Duration % Children]@row))
    • "IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row" part does the same calculation for the grandparent row.

Lastly, I applied the same logic to calculate [% Hour Complete].

• [Target Hours SUM]=IF([is Parent]@row, SUM(CHILDREN([Target Hours]@row)))
• [Hour % Children]==IF(Anc@row = 0, SUM(CHILDREN([Hour % Parent]@row)), [Target Hours]@row / PARENT([Target Hours SUM]@row))
• [% Hour Complete]=IF(Anc@row = 0, SUM(CHILDREN([Hour % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Hour % Children]@row))

It would have been easier if I calculated the simple average of the [% Complete] or [% Hour Completed]. Still, Smartsheet's weighted average approach gives me a more accurate completion rate, considering each completed child's relative length.

https://app.smartsheet.com/b/publish?EQBCT=ce03d423be264626919ca34a30a96505

Tedd Powers

Thanks so much. This was very helpful and solved my issue.

jmyzk_cloudsmart_jp

Happy to help.😁

Julie Barbeau

@jmyzk_cloudsmart_jp Hi, is there a way to make this works out if some tasks has 0 target hour? As we used some row for a kind of check list but no hours allocated to this row. Currently I have DIVIDE BY HERO error

jmyzk_cloudsmart_jp

@Julie Barbeau

As I tested, if all the children's task hours of a given parent are zero, the formula gives the divide by zero error. So, I added the IF clause in bold.

=IF(Anc@row = 0, SUM(CHILDREN([Hour % Parent]@row)), IF(PARENT([Target Hours SUM]@row) = 0, 0, [Target Hours]@row / PARENT([Target Hours SUM]@row)))

Julie Barbeau

@jmyzk_cloudsmart_jp I tried this in Hour % Children but the result doesn't make sense, % Hour Complete is now the same as Hours % Children.

Test % heure.png

Humashankar

Cool !!