Conditional Formatting Excel

Charlotte Moss 21 Reputation points

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,415 questions
{count} votes

Accepted answer
  1. Emi Zhang-MSFT 13,081 Reputation points Microsoft Vendor

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Charlotte Moss 21 Reputation points

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

    0 comments No comments