Conditional Formatting - Before and after expiry date

Question

I'm trying to set up conditional formatting for a spreadsheet. I have a date when I received a consent letter, I'd like this to be coloured green if consent is still valid and red if consent has expired (18 months after receipt of letter).

Any suggestions appreciated, TIA!

Answer 1

Hello,

So to start off, 18 months ago was Sunday July 25, 2021.

I have two columns (Name, Received).

Highlight your column you want conditional formatting on > go to Conditional Formatting > New Rule.

Now go to Use a formula to determine which cells to format > Enter your column (in my case it is B1) as =B1<TODAY()-547.506

The 547.506 is an approximation for 18 months ago. Press OK and it should highlight like my example above.

Do the same for adding the rule for 18 months ago or greater:

You will then get as a final result:

---

If this is helpful please accept answer.