Conditional Formatting Excel

Question

I have taken over an excel spreadsheet and would like the dates to change colour as they get close to expiring but I'm having trouble, What I would like is the below

Red - A year after the original date entered
Amber - 30 days before we reach the next year
Green - From date recorded until 30 days when it would change to Amber

I have found formulas basing it on today but the dates I have are from 2021 or dates earlier than today of this year and the formulas I've found and tried just turn everything red or Amber.

So what I'd like is that is I did something on 03/06/2022 I want it to go red on 03/06/2023 and amber on 03/03/3023.

Answer 1

Hi @Charlotte Moss ,
Select the range you need to apply Conditional Formatting- go to Home- Conditional Formatting- New Rule...- Use a formula to determine which cells to format- enter the formula and set the cell format:

Green: =TODAY()-A1<335
Amber: =AND(TODAY()-A1<365,TODAY()-A1>=335)
Red: =TODAY()-A1>=365

If my understanding is incorrect, please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you so much this is exactly what I was after.