Sep 17 2024 04:53 AM - edited Sep 17 2024 04:55 AM
I need help with a formula for - Green > one month past todays date, Red =< todays date, amber <= one month todays date.
Sep 17 2024 05:01 AM
SolutionSelect 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.
Sep 17 2024 05:05 AM - edited Sep 17 2024 05:08 AM
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 rules
the advantage of the multiple rules is you get 'pure' color while the above single rule approach give a gradient when you transition
Sep 17 2024 05:01 AM
SolutionSelect 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.