Conditional Formula for highlighting dates greater than 7 days

[SomethngWicked]

Hi Excel Wizards,

I've been trying to figure out a solution for the following problem. In one column, I have a series of dates that stretch back between 1 day to several years. I'm trying to come up with a formula that would highlight the cell as being red if the time is > 14 days, yellow if greater than 8-13 days, and green if it's <7 days.

So for example, cell A1 has a value of 12/17/2018 1:50:04 PM. This should be highlighted red. Cell A2 has a value of 1/9/2019 2:00:11 PM and should be green. Cell A3 has a value of 1/1/2019 1:47:21 PM and should be yellow.

Thanks in advance!

 

[Special-K99]

I think this should work, this assumes your dates AND times are proper Excel dates and times

Select the range first, e.g. A1:A1000

Conditional Formatting
New Rule
Use a formula to determine...

=AND(A1>=TODAY(),A1<=TODAY()+13)
format as yellow

=AND(A1>=TODAY(),A1<=TODAY()+7)
format as green

=AND(A1>=TODAY(),A1>TODAY()+14)
format as red


UPDATE: Just understand your requirements, go with Scott's answer, mine is wrong.

 

[Scott T]

You will need a rule for each possible color

for red

=NOW()-A1>14

for green

=AND(NOW()-A1>7,NOW()-A1<14)

for Yellow

=NOW()-A1<=7

 

[SomethngWicked]

You will need a rule for each possible color

for red

=NOW()-A1>14

for green

=AND(NOW()-A1>7,NOW()-A1<14)

for Yellow

=NOW()-A1<=7

I think this will work. I had to change the formula out for green and yellow though. Is it possible to exclude this formula from blank cells? Thank you for your help!

 

[Scott T]

this will not format if A1 is blank.

=AND(NOW()-A1>14,A1<>"")

 

[SomethngWicked]

Thanks, Scott!

 

[Eric W]

Just for information, you can do it with one rule if you use icon sets. In this case, select your range, click Conditional Formatting > Icon Sets > and choose any 3-icon set. Then click Conditional Formatting > Manage Rules > select your rule > Edit Rule. Now go to the top box that says Type: change it to Formula, and in the Value box put =TODAY()-7. On the next row down change the type to Formula, and in the Value box put: =TODAY()-14. Click OK.

 

[SomethngWicked]

Very Cool - Thanks!