Conditional Formatting

Cheryl Collins

Hi

I have a sheet where the user would like to have rows highlighted in Red, Amber, Green based on when the last review date was completed:

Review in the last 10 months - Green

Review in last 11 months - Amber

Review in last 12 months (or more) - Red

I'm unsure the best way of doing this - do I need a formula in a helper column that I can look my conditional formatting rule up to, do I do a report that groups by months?

Any ideas greatly received on the best way to achieve getting this information.

Many thanks

Cheryl

Paul Newcome

Try changing it to "is in the past".

Paul Newcome

You should be able to do this directly in conditional formatting as long as you stack the rules in the proper order (it will highlight the color based on the first true rule starting from top to bottom).

Select your date column and set it to be when the date is in the last 305 days for Green.

The next one down would be 335 days for Amber.

The third one down would be 365 days for Red.

Cheryl Collins

Hi @Paul Newcome

Thanks for responding. I have added the conditional formatting as per your suggestion however, my red rule doesn't appear to be working unless it's bang on 365 it needs to be 365 or more?

Paul Newcome

Try changing it to "is in the past".

Cheryl Collins

Hi @Paul Newcome That's done it - thanks

Paul Newcome

Excellent! Happy to help. 👍️