Have Children Status based on due date but have parent roll up based on all of the children status

Mason B

Hey All,

I am having some difficulty getting a formula to work the way I want to here.

I want all children rows to look at the due date column and if it is overdue, mark the status as "Red". If todays date is within 3 days, mark the status as "Yellow". If todays date is over 3 days, make it as "Green". I just want these applied to the children rows, I would like the parent row of this status column to look at its children and if it has 1 "Red" status in its children, mark the parent row as "Red", if it has 1 "Yellow" status in its children, mark the parent as "Yellow", and if all its children are "Green", mark the parent as "Green".

I feel like this shouldn't be that difficult but need some fresh eyes here. I know the below is way off but this is what I was trying before:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([Due Date]@row > TODAY(), "Red", IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([Due Date]@row >= (TODAY() - 3), "Yellow", "Green") IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow", )))))

How I am hoping the formula would look like:

Jake Kenyon

Hey Mason!

The order of operations here makes a big difference, by starting with the criteria you would like for the parent rows, that helps simplify the criteria needed once you get to the the child rows. The first thing I would do is add a column with a column formula called Children, containing the formula =COUNT(CHILDREN()). I find it easiest to have this as its own column for easy reference and sorting, but you can always replace any reference to that column in the formula with the formula itself if you prefer.

The formula as written uses blue to represent a complete task, so you can remove the first criteria of the formula if that is not something that you would like included. The formula I generally use is as follows:

=IF([% Complete]@row = 1, "Blue", IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Red")) >= 1, "Red", IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Yellow")) >= 1, "Yellow", IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row - TODAY() < 3, [% Complete]@row < 1), "Yellow", "Green")))))

By putting the criteria for the parent rows first, when Smartshet parses the formula sequentially it only applies the latter criteria to the child tasks, as it has already eliminated the parent tasks as options. This also allows you to have a parent task for the whole project as the first line which gives you an overall project health. Let me know if that formula works for you!

KPH

What if the children are one red and one yellow, is that still red?

We need to nest your IFs with each successive IF only being applied if the previous ones are false. You probably don't need the ANDs if you lay the logic out in a logical(!) order and your formula will be less complex.

So your first IF would be to check if the row is a parent - if true check second formula (see below - this gets pasted in)

If not a parent check for status due in the past - mark red if true

It not a parent and not overdue days check if status is within 3 days - mark as yellow if true

If not a parent and not overdue and not due check is status is within 3 days - mark as green

Second formula

If one child is red - mark in red if true

if no children are red and one child is yellow - mark in yellow if true

if no children are red and none are yellow - mark in green

So my question is if the part in bold is what you want. If so, we can write an IF for each step and nest them together.

Jake Kenyon

Hey Mason!

The order of operations here makes a big difference, by starting with the criteria you would like for the parent rows, that helps simplify the criteria needed once you get to the the child rows. The first thing I would do is add a column with a column formula called Children, containing the formula =COUNT(CHILDREN()). I find it easiest to have this as its own column for easy reference and sorting, but you can always replace any reference to that column in the formula with the formula itself if you prefer.

The formula as written uses blue to represent a complete task, so you can remove the first criteria of the formula if that is not something that you would like included. The formula I generally use is as follows:

=IF([% Complete]@row = 1, "Blue", IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Red")) >= 1, "Red", IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Yellow")) >= 1, "Yellow", IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row - TODAY() < 3, [% Complete]@row < 1), "Yellow", "Green")))))

By putting the criteria for the parent rows first, when Smartshet parses the formula sequentially it only applies the latter criteria to the child tasks, as it has already eliminated the parent tasks as options. This also allows you to have a parent task for the whole project as the first line which gives you an overall project health. Let me know if that formula works for you!

Mason B

Worked liked a charm, thank you Jake! Here is the formula I generated from yours:

=IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Red")) >= 1, "Red", IF(COUNT(COLLECT(CHILDREN([Task Name]@row), CHILDREN(), @cell = "Yellow")) >= 1, "Yellow", IF(AND([Due Date]@row < TODAY(), Children@row < 1), "Red", IF(AND([Due Date]@row - TODAY() < 3, Children@row < 1), "Yellow", "Green"))))

Jake Kenyon

Awesome! Glad you were able to make it work!