Highlight parent if child task is in the past

Jett

Hi I have a sheet that has a column called Task name it has a main task with 4 subtasks (child) below it. The sheet has a Task name, Status and due date column. If the 1st sub task due date is in the past and not marked complete how do I have it highlight the parent of this task Red.

If the 1st is complete it is no color if the second task is in the past it will mark the parent red Etc.

Thanks!

JBG

Hi Jett,

You need to create a helper column to tag all parents who might have an active child who is in the past. In that column, you could use a formula as per below.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0; IF(AND(Status@row <> "Completed"; [Due Date]@row < TODAY()); "Failed"; "Passed"); IF(COUNTIF(CHILDREN(IncompleteChildInPast@row); "Failed") > 0; "Tag as Red"; ""))

IF(COUNT(CHILDREN([Task Name]@row)) = 0 check if the row is a child,

if the row is a child, then this is executed: IF(AND(Status@row <> "Completed"; [Due Date]@row < TODAY()); "Failed"; "Passed"). Validate the status and due date. If the status is not "completed" and the date is in the past, then the child is tagged as failed, otherwise, it is tagged as passed.

if the row is a parent, then this is executed: IF(COUNTIF(CHILDREN(IncompleteChildInPast@row); "Failed") > 0; "Tag as Red"; "")). If there are children that failed then the parent is marked as "Tag as Red".

With Conditional formatting, you can then use this helper column to highlight the row as red.

NOTE: The separator is a semicolon due to my computer settings. You might need to use comma!!

I hope this helps.

====================================================

"Nothing is impossible. The word itself says 'I'm possible!"