% 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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/13/23 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.)
    • 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.


Answers

  • jmyzk_cloudsmart_jp
    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


  • 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
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/13/23 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.)
    • 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.


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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Happy to help.😁