Forum Discussion
beccaw745
Jul 19, 2024Copper Contributor
Help with formula
Hi!
I'm new to excel and am trying to make a date list for my job. I'm wanting to have an expiration date automatically turn colors depending on status. Example, EXP : 6/02/2025 turn it green until 4/02/2025, yellow once 4/02/2025 hits until 5/02/2025 and then turn red on that date. Can anyone help? TIA
Select the range with the expiration dates.
Set the fill color to green. This will be the default.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=EDATE(TODAY(), 2)
Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.Repeat these steps, but with
=EDATE(TODAY(), 1)
and red as fill color.
If you have blank cells in the range, add another rule with 'equal to', ="" and No color.
Select the range with the expiration dates.
Set the fill color to green. This will be the default.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=EDATE(TODAY(), 2)
Click Format...
Activate the Fill tab.
Select yellow as fill color.
Click OK, then click OK again.Repeat these steps, but with
=EDATE(TODAY(), 1)
and red as fill color.
If you have blank cells in the range, add another rule with 'equal to', ="" and No color.