Conditional Formatting

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try changing it to "is in the past".

    thinkspi.com

Answers

  • Paul Newcome
    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.

    thinkspi.com

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    edited 04/19/22

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try changing it to "is in the past".

    thinkspi.com

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭

    Hi @Paul Newcome That's done it - thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Happy to help. 👍️

    thinkspi.com