Forum Discussion
Excel Help with Conditional Formatting -dates
I need help with a formula for - Green > one month past todays date, Red =< todays date, amber <= one month todays date.
Select the cells with the 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(), 1)
Click Format...
Activate the Fill tab.
Select amber as fill color.
Click OK, then click OK again.Repeat these steps, but with
=TODAY()
and red as fill color.
Finally, repeat with 'equal to' instead of 'less than or equal to', with
=""
and No Color as fill color.
This removes the fill color from empty cells.
you have a couple options:
you can set up at 2 or 3 conditional formatting rules each one using a formula like <=today()
or you can use 3 color rule and base each color on 'number' and use the same date rulesthe advantage of the multiple rules is you get 'pure' color while the above single rule approach give a gradient when you transition
Select the cells with the 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(), 1)
Click Format...
Activate the Fill tab.
Select amber as fill color.
Click OK, then click OK again.Repeat these steps, but with
=TODAY()
and red as fill color.
Finally, repeat with 'equal to' instead of 'less than or equal to', with
=""
and No Color as fill color.
This removes the fill color from empty cells.
