Formula to Overwrite Conditional Formatting or Help to Figure Out Another Solution

Chelsea Duncan

Hello!

When a date is entered in Date Assigned to ID, I have a formula that populates the Analysis Due Date for 30 days after (=[Date Assigned to ID]1 + 30). I also have conditional formatting on the Analysis Due Date that changes colors based on days out from 30 days.

What I need to do is change Analysis Due Date back to white when the TRB Authorization (row 14) has a date in Date Completed.

Thank you :)

Paul Newcome

Conditional Formatting works from the top down, so what if you were to rearrange the order of the rules?

Paul Newcome

You will need to add in a condition on the conditional formatting rule to only apply to rows that meet the already established criteria AND the Date Completed column is blank.

This way when the Date Completed column is not blank it will no longer meet the criteria for the Conditional Formatting.

Chelsea Duncan

I tried to add a 3rd separate condition:

If Task Name contains 'TRB Authorization' and Date Completed is a date then apply format to the Analysis Due Date Column.

This isn't working. This rule comes after the other two rules that apply to the Analysis Due Date column

Chelsea Duncan

@Paul Newcome

I know why it won't change the color, it's because when the analysis due date is in the past, it turns red. So this rule must take priority over the new one I want because no matter what, this date, when overdue, will stay red because the date is always in there that's past.

So that's why I'm hoping for a formula that may turn off that condition.

Paul Newcome

Conditional Formatting works from the top down, so what if you were to rearrange the order of the rules?

Chelsea Duncan

BAM!!! that's it! thank you so much!!

Paul Newcome

Happy to help. 👍️