conditional format on date

[Peter1973]

Hi I am trying to get a formula for a conditional format to look in A1 and if the date is less than 14 days away from today then go amber and if it is less than 7 days away from today go red.

 

[Joe4]

You can do this with Conditional Formatting formulas:

For red:

=AND(A1-TODAY()<7,A1-TODAY()>=0)

For amber:

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

 

[JoeMo]

Hi I am trying to get a formula for a conditional format to look in A1 and if the date is less than 14 days away from today then go amber and if it is less than 7 days away from today go red.

I'll assume that "away from" today means on either side of today, as opposed to "before" today or "after" today. You can adjust the formulas below to suit the latter cases.
1st rule CF formula:
=ABS(TODAY()-A1) < 7
2nd rule CF formula:
=ABS(TODAY()-A1) < 14

 

[Joe4]

Just note some subtle differences to our responses, and be aware how Excel handles these.

- I assumed you were talking about future dates (so A1 would hold future dates). JoeMo assumed past dates (so A1 would hold past dates). Whichever one is bigger needs to be first in the subtraction expression.
- I included a date range, so order of the Conditional Formatting rules does not matter. JoeMo only checks the end date, so order does matter. And note that when entering CF rules, Excel does it in reverse order so that the LAST condition you enter is the 1st priority. The good news is that if you do happen to enter them in the wrong order, it is easy to edit the rules and re-order them.

 

[JoeMo]

Just note some subtle differences to our responses, and be aware how Excel handles these.

- I assumed you were talking about future dates (so A1 would hold future dates). JoeMo assumed past dates (so A1 would hold past dates).

Actually, I assumed that "away from" today includes both dates before today and dates after today as I stated in my post.

 

[Joe4]

Actually, I assumed that "away from" today includes both dates before today and dates after today as I stated in my post.

Ah yes, I was just looking at the formulas and overlooked the ABS part of the formula! Sorry for the confusion!

 

[JoeMo]

Ah yes, I was just looking at the formulas and overlooked the ABS part of the formula! Sorry for the confusion!

Thanks, just wanted the OP to recognize that choice of words can be important. In this case, "away from today" can be either, or both of, "before today" and "after today".