Project Compliance for tasks due up to Today

Randy Van Winkle

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.

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

Randy Van Winkle

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

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].

Randy Van Winkle

@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.

Randy Van Winkle

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