Microsoft Excel: Conditional Formatting of Dates (Within Certain Ranges)

1
votes

Trying to do conditional formatting in excel and it seems a little tricky for my data.

I have 654 dates. 654 rows/entries.

I need to use three colors:

Green - Make the cells green that are within 10 MONTHS OF TODAY'S DATE

Yellow - Make the cells yellow that are within 10 TO 12 MONTHS OF TODAY'S DATE

RED - Make the cells red that more than 12 MONTHS away from today's date OR do not have any data in the cell

I know this isn't correct but I feel like I'm on the right track:

red=($G$428:$G$655<=TODAY() + 365) how would I accommodate blank cells in this formula?

yellow=($G$3:$G$427<=TODAY() + 300 to 365) How would you correctly specify 'between 10 and 12 months'?

green=($G$3:$G$427<=TODAY() + 300)

Any input is appreciated.

1
exceldaterangeconditional-formatting
Do you need to check in both directions (past/future dates) ? – Tim Williams
Yes, I certainly do. Sorry I should have pointed that out. – Nubtacular

1 Answers

2
votes

My test range was B3:B35 (selected before applying the rules)

=ABS(B3-TODAY())<=300 ' <10 months

=OR(B3="",ABS(B3-TODAY())>365)  'more than 1 year or empty

=ABS((B3-TODAY())>300) ' >10 months (rule above takes care of >12 mo)