Conditional formatting assistance please

Copper Contributor

How do I highlight a cell yellow if the date is 30 days out?

How do I highlight a cell red if the date is today or passed?

How do I leave a cell alone if there is no date in the cell?

All 3 rules would be for 1 cell.

Thanks in advance!

8 Replies

@c_frndz 

You may use with formulas

red
=A1>=TODAY()

yellow
=(TODAY()-A1)<30

the rest won't be formatted

@Sergei Baklan Thank you Sergei. Please see attached. It doesn't seem like the red formula worked unless i did it wrong. Please advise.

Red.png

Yellow.png

  

@c_frndz 

Sorry, it shall be the opposite

=TODAY()>=$G$19

to check if the date in the cell is in the past

Thank you again. However, now the cell will remain remain even with no data.

@c_frndz 

Forgot about blanks

=(TODAY()>=$G$19)*($G$19>1)
Sergei,

Not sure what going on as I entered the formula as you have it. However, when I enter a date in the cell like 1/30/20 it highlights the cell yellow rather than red. And when I enter 3/30/20 it’s still yellow. Thanks again for your time.

@c_frndz 

Is it shall be as here?

image.png

Please check CF rules in attached file

@Sergei Baklan I thank you for all your help and apologize as I'm a little confused. So basically i want a cell to turn yellow when it is 30 days or less from the expiration date. Turn red once it reaches or surpassses the the expiration date. Have no color when there is no data in the cell.