Change the color of a cell based on a range of dates

[Jvolquartsen]

I have a spreadsheet in which Column E is calculating a date based upon a date entered in Column A and adding a set number of days to it.

I wish to have column E shaded to a given color if the calculated date is between today and 30 days from now

[#2 · Accepted solution]

Select column E and then go to Home->Conditional Formatting and Add New and then select Based on Formula.
Then in the formula box use:
=(E1>Today())*(E1<(Today()+30))
Make sure to click Formatting and select the background color you want.

[#3]

Thank you it was the * that I was missing, but you also have one extra ")" at the end of your formula.

I appreciate the assistance!

[#4]

You're very welcome. Alternatively you could use AND().
As for having an extra ")" in the formula I don't see it, but since you got it to work i will close this out for you

[#5]

If I need a cell to change color by example case open on 08/4/23 and I need to cell to be red if case open for more than 14 days - yellow for more than 7 days and green for less than 7 day. I'm trying to get it right with all the post I see here but I can't get it to work :(

[#6]

@joanniex44 

Select 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' from the second drop down.
In the box next to it, enter the formula

=TODAY()-7

Click Format...
Activate the Fill tab.
Select yellow as highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula

=TODAY()-14

and red as highlight color.