SOLVED

Excel Help with Conditional Formatting -dates

Copper Contributor

I need help with a formula for - Green > one month past todays date, Red =< todays date, amber <= one month todays date.

Lennox2525_0-1726573961583.png

 

 

 

2 Replies
best response confirmed by Lennox2525 (Copper Contributor)
Solution

@Lennox2525 

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 rules

m_tarler_0-1726574826596.png

the advantage of the multiple rules is you get 'pure' color while the above single rule approach give a gradient when you transition

 

1 best response

Accepted Solutions
best response confirmed by Lennox2525 (Copper Contributor)
Solution

@Lennox2525 

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.

View solution in original post