SOLVED

Cell color changes when registration date nearing expiry?

Copper Contributor

I am creating a spreadsheet for vehicle registration and I would like the cell color to change when it is nearing expiry. Is this possible?  Thanks in advance

 

- Cell color RED if date in cell is today's date or older

- Cell color ORANGE if date in cell is between tomorrow's date and 1 months time

- Cell color GREEN if date in cell is more than 1 month from today

user1234321_0-1661224674853.png

 

12 Replies

@user1234321 You need 3 conditional formatting rule. See attached file.

For red =A2<=TODAY()
For orange =AND(A2-TODAY()>=1,A2-TODAY()<=31)
For green =A2-TODAY()>31

 

Harun24HR_0-1661233088859.png

 

 

@user1234321 

How To Conditional Formatting Based On Date In Excel?

  • equal to today:=$B2=TODAY()
  • reater than today: =$B2>TODAY()
  • Older than today: =$B2<TODAY()

 

Hope I could help you with these information / link.

 

NikolinoDE

I know I don't know anything (Socrates)

best response confirmed by user1234321 (Copper Contributor)
Solution
Thankyou for this, much appreciated.
Can I duplicate these rules to other cells without manually changing A2 to the new cell, eg A3?
You do not need to duplicate the rules. It will automatically apply to all cells given to Applies To range.

I am working with Google sheets - when I add the custom formula to Conditional format rules it applies A2 only

user1234321_0-1661235513845.png

 

Your image would suggest that you have been successful in applying the conditional format to cells A2 and A17. I would, however, suggest that you are sparing with your use of colour. It is difficult to read text against a dark colour and overuse reduces its impact.
It does change colour however is using A2 value, not A3, A4 etc. All cells end up being the same colour
The image shows A1,A17. That is A1 and A17, not A1 to A17. The range is written A1:A17.
I hope that helps.

It does help, thank you for that.
I have a date that should be orange but instead it is white. Any idea's why the orange is not taking effect?

 

user1234321_0-1661254139606.png

 

Most likely the green CF has worked but there is still an error in the red or yellow.

@Harun24HR
The green is working thank you, however if it meets the value of Orange it changes to white

Please disregard - I re-entered the formulas and it is working. Thanks so much everyone
1 best response

Accepted Solutions
best response confirmed by user1234321 (Copper Contributor)
Solution
Thankyou for this, much appreciated.
Can I duplicate these rules to other cells without manually changing A2 to the new cell, eg A3?

View solution in original post