% 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.😁
-
@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
-
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)))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives