Conditional Formatting Based on Text and Date Range

[welshraz]

I don't know why I can't grasp this, but I want to apply conditional formatting to column J based upon the text in column A, and then the date in column J.

Essentially, if column A does not include the words "Cancelled" or "Completed", I want to highlight the corresponding cell in column J, but the colour then needs to be dependent on how far away that date is from the current day: 10 days or less, 90 days or less, 183 days or less.

<> "Cancelled" <> "Completed" and less than 10 days from the current date - Red
<> "Cancelled" <> "Completed" and less than 10 days from the current date - Amber
<> "Cancelled" <> "Completed" and less than 10 days from the current date - Yellow

Everything else should be left with no fill. Thanks!

 

[Joe4]

Select column J, and then enter these CF formulas:

For Red:

=and(A1<>"Cancelled",A1<>"Completed",today()-J1<10)

For Amber:

=and(A1<>"Cancelled",A1<>"Completed",today()-J1>=10,today()-J1<90)

For yellow:

=and(A1<>"Cancelled",A1<>"Completed",today()-J1>=90,today()-J1<183)