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

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.)
 [Duration % Children]=IF(Anc@row > 0, Duration@row / PARENT([Duration SUM]@row))
 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.
 [% Complete by Formula] =IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Duration % Children]@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.
 number of hours completed = [Target Hours]@row * [% Complete]@row
Answers

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

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.

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.)
 [Duration % Children]=IF(Anc@row > 0, Duration@row / PARENT([Duration SUM]@row))
 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.
 [% Complete by Formula] =IF(Anc@row = 0, SUM(CHILDREN([Duration % Parent]@row)), IF([is Parent]@row, SUM(CHILDREN()), [% Complete]@row * [Duration % Children]@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.
 number of hours completed = [Target Hours]@row * [% Complete]@row

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

Happy to help.😁