Project Compliance for tasks due up to Today

Hello, I am trying to figure out a way to gauge Project Compliance up to Today's date. What percentage of tasks are 100% complete that were due on or before today. In the example the compliance would be 75%. 4 tasks were due prior to today's date and 3 are marked 100%. Task 5 does not figure in because due date is in the future. I have a fairly complex project template with different departments in the parent rows. I would like to figure compliance by each department based on the child rows for that department.


Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Randy Van Winkle

    This formula should work for the Compliance column of those parent rows:

    =COUNTIFS(CHILDREN([%Done]@row), @cell = 1, CHILDREN([End Date]@row), @cell <= TODAY()) / COUNTIF(CHILDREN([End Date]@row), @cell <= TODAY())


    Let me know if it works!

    Best,

    Heather

  • I got #UNPARSEABLE when I plug that into the yellow cell on my example

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Randy, it is not very clear from your sheet screenshot but may be there should be a space between % and Done in the column name used by Heather in the formula provided above. Try changing [%Done] to [% Done].

  • @SK That did seem to correct the problem. However, I did not account for another variable in my description. As noted the project template is rather complex and as such has a number of tasks with subtasks. I figured that I could replace Children with Descendants as such

    =COUNTIFS(DESCENDANTS([% Done]@row), @cell = 1, DESCENDANTS([End Date]@row), @cell <= TODAY()) / COUNTIF(DESCENDANTS([End Date]@row), @cell <= TODAY())

    However, when I do that the Child Row and the Grandchild Row get counted in the calculation. Is there a way to exclude the Child Row so that compliance is not affected twice on the same non-compliant task?

    In the revised example Task 1 through 8 have due date in the past. Only task 5 is out of compliance, but the Percent calculated is based on 7 out of 9 not compliant including "Category 1" as another task.


  • Bump. Does anyone have any feedback on this? Is there a different way to gauge task compliance up to current date?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!