Automate RYGB and Children together

GarryD

I have a standard project sheet.  I added one column called Today that in Row1 links to a separate sheet that will be triggered everyday so that it gets Today's date (this gets you around using the Today function which only triggers when a sheet is opened), every row below that first row then has a =CellReference the cell above so that as rows get added to a sheet the calculation populates in new row, allowing a calculation in the RYBG column to populate as well and always have the Today date to use in the same row. (PS must have it as a data column but not restricted).

The rest is standard Task name, Start, Finish, % complete and then my RYBG column.  Blue = 100% complete,  If row not 100% then: Yellow = finish date equal to or within 2 days of Today, Red = finish date less than today (so missed it), Green = Finish date not missed and have more than 2 days yet.

I have this equation made: it almost works except having trouble with Green because the equation will yield a negative number -1 - XX as having that many days yet.

=IF(Finish36 = "", "Error", IF([% Complete]36 = 1, "Blue", IF((Today36 - Finish36) = 0, "Yellow", IF((Today36 - Finish36) = 1, "Yellow", IF((Today36 - Finish36) = 2, "Yellow", IF(Today36 > Finish36, "Red", IF((Finish36 - Today36) > 0, "Green", "NA")))))))

Now I also want to have it apply logic to the parent row.  Meaning if you use the formula above you can have a green parent row with all red children.   I want the parent to be the color of the children based on the number of children a given color.  So I use this equation:

 

IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))

 

So question now is:

1. how to make the first equation work properly for Finish - Today equaling a negative number and not breaking the rest of the equation (have a feeling easy fix but been looking at too many equations)

2. marry equation 1 and 2 together so that the RYBG column has the equation all the time and populates if rows are added.  PS: equation 2 doesn't address Blue at all because equation 1 would turn each row blue and if all blue then parent turns blue on that same equation and if anything not blue I don't want the parent ever showing Blue because Blue means 100% done.

 

So who can help?  Happy to share the sheet also if anyone want it.

Thanks

Garry

 

 

 

GarryD

I believe I now have equation 1 working properly for RYBG on non-parent rows.

=IF(Finish2 = "", "Error", IF([% Complete]2 = 1, "Blue", IF((Finish2 - Today2) = 0, "Yellow", IF((Finish2 - Today2) = 1, "Yellow", IF((Finish2 - Today2) = 2, "Yellow", IF(Today2 > Finish2, "Red", IF((Finish2 - Today2) > 0, "Green", "NA")))))))

 

So now how do you combine Eq1 & 2 so that the sheet (general user adding a row) doesn't need to worry about the formula it will work if it is a parent or child?