enter image description here

I want to create a RAG system in google sheets based on dates in a cell. Column A will show a date when someone is trained on a topic and column B shows the due refresher date (3 years later). I want to set up a RAG system so that column B will show green if the dates are within 2 years from the training date, amber when there is one year left before the refresher is due and red when the refresher period has passed.

  • This doesn't look like a practical, detailed question about using Google Sheets. I'm not adding more detail as it will likely be closed and eventually deleted after the moderation strike.
    – Rubén
    Jun 22 at 17:52

1 Answer 1


Try these conditional formatting custom formula rules for the range B2:B:

green: =today() <= B2 - 2 * 365

yellow: =today() <= B2 - 365

orange: =today() <= B2

red: =len(B2) * (today() >= B2)

  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. May 4, 2021 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.