Conditional Formatting by Date Range

Hey guys,

I'm trying to set up a 3-colour scale for a column of dates. I need the following criteria to apply:

Cells turn green if date is within the past 0 - 30 days
Cells turn yellow if date is within the past 30 - 40 days
Cells turn red if the date is past 40 days

I thought that I could set this up using a formula for each but I am not sure what I would input in particular. Do you know how I would do that?

I've attached an image to show my current thinking.

Any help would be really great!

Thanks - Amba

Screenshot 2019-11-23 at 10.09.04.png

Hi and welcome

You could set up three different CF rules that use formulas.

Green = =A1 >=TODAY()-30
Yellow = =A1 >=TODAY()-40
Red = =A1< TODAY()-40

In that order.

See attached.

Beth.

Hi Beth,

Thanks so much for your quick response - and good to know it can be done quite simply.

Unfortunately I might be inputting it wrong - as it is giving me the following message about relative references in conditional formulas.

Do you know what I am doing wrong?

A.

Attachment 651063

Rather than use the three colour rule window that you show in post#1, click "Home / Conditional Formatting / New Rule / Use a formula to determine which cells to format", then add the rules one at a time.

Attachment 651064

Beth.