Automating RYG balls based on completed dates in parent row OR children rows

Cathy Holsing

Hi all,

I have a sheet with parent rows where the status column is updated with a RYG ball based on the completion date and due date of the row: 

=IF(ISBLANK([Due Date]108), "", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow"))))

The children below this parent have the same formulas in their individual rows.  

What I'd like to do now is add the ability to add a blue ball to the parent row based on any child row being completed.  (So if the parent row does not have a completed date entered, blue would show that something is in process based on one of the child tasks).  

I can get this to work to show the blue ball,

IF(COUNTIF(CHILDREN(), "Green") > 0, "Blue")

but I don't know if it is possible to combine the two formulas.  Any help would be greatly appreciated!

 

 

Greg Gates

It's definitely possible! You just have to decide what priority the blue status ball should have. Then, you can insert into your nested if-statement accordingly. So, for example, if you think it's more important to show the blue ball than the red/yellow ball you could write your formula like this:

 

=IF(ISBLANK([Due Date]108), "", IF(COUNTIF(CHILDREN(), "Green") > 0, "Blue", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow")))))

 

If you only want to show blue *after* checking to see if it's red or yellow, you could put it at the end:

 

=IF(ISBLANK([Due Date]108), "", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow", IF(COUNTIF(CHILDREN(), "Green"), "Blue", ""))))

 

I hope that's helpful!