Another parent-child condition format scenario

WaterShield

I've search Community and found several very excellent formulas that fall into the parent-child conditional format category; however, they rely on a checkbox type to function. In my scenario, the child row has a conditional format that when a date is within 15 days of target date, the row is highlighted. I'd like the parent row to adopt the child condition when applicable.

For example, child sub-task has a target date of 4/3/18. Within 15 days of this date, a conditional format applies to the sub-task row. I want to apply this same condition to the parent row. When I apply a formula such as =if(ColumnName1<today()-30),1,0) and parent formula =sum(children())*(-1) gleaned from Community, it parses out, blocked, etc. How can I build the formula to use a date type?

 

 

Chris McKay

Hi WaterShield,

Is the following what you are after?

=IF(ISBLANK([Column Name]1), "", IF(AND(COUNT(PARENT([Column Name]1)) > 0, [Column Name]1 < TODAY() - 30), 1, IF(AND(COUNT(PARENT([Column Name]1)) = 0, COUNTIF(CHILDREN([Column Name]1), <(TODAY() - 30)) > 0), 1, 0)))

Copying this into another column (e.g. a checkbox column) will evaluate on the criteria you mentioned. You can use the same formula on all rows as it will adjust itself to deal with parent or child row types automatically and will leave the cell blank if there is no date value in a row (instead of returning a 0).

WaterShield

Chris,

Your formula seems to work in this scenario; however, I need the formula to apply the condition of child row to the parent. For example, if the future date is within 20 days of current date, apply yellow highlight to child row and parent row (see attached screenshot). I added the formula to the Current Inspection Scheduled column (checkbox type).

Thanks for your help!