I have created sheets to track critical HR tasks that are performed on regular basis. All is working as expected (thanks to help from a few of you) but this morning was thrown a curve ball.
USE CASE: This one tasks has ten check points in process. Of the ten, the last step is only late 4 business days after the previous nine steps. The team agreed that the first nine are the critical ones to track if late. I need to update formula to exclude the tenth task. In this case I added column “Late Date Calculation – NO”. I would like to modify this to check if the flag is checked and if so then ignore this row in the update to PARENT Row. I need an additional IF/AND or similar logic to ignore this child row and proceed with rest of logic. Is this possible or is there a better way to evaluate the children data and roll up to parent?
This formula is used in hidden in helper column that is then used for an automation to update the task status. The Task Status children are updated by the HR analyst, used to drive the helper column. Based on the logic in helper column the parent row is updated which triggers automation.
=IF([Step Number]@row = 0, IF(COUNTIF(CHILDREN([Task Status]@row), "") + COUNTIF(CHILDREN([Task Status]@row), "Not Started") = COUNT(CHILDREN(Week@row)), "Not Started", IF(COUNTIF(CHILDREN([Task Status]@row), "Complete") = COUNT(CHILDREN(Week@row)), "Complete", IF(COUNTIF(CHILDREN([Task Status]@row), "N/A") = COUNT(CHILDREN(Week@row)), "N/A", IF(COUNTIF(CHILDREN([Task Status]@row), "Error") > 0, "Error", IF(COUNTIF(CHILDREN([Task Status]@row), "Retest") > 0, "Retest", IF(COUNTIF(CHILDREN([Task Status]@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN([Task Status]@row), "Not Started") > 0, "In Progress", "Complete"))))))), IF([Task Status]@row = "", "Not Started", [Task Status]@row))
Note: I will add another automation to notify the analyst and manager if the excluded (step 10) task is late and should be able to report this as separate activity in my reporting and dashboard.
