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

Question

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.

Do you need to check in both directions (past/future dates) ? — Tim Williams
– Tim Williams, Commented Feb 15, 2013 at 17:55

Tim Williams · Accepted Answer · 2013-02-15 18:04:41Z

2

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)

answered Feb 15, 2013 at 18:04

Tim Williams

169k8 gold badges103 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter L. Over a year ago

I'd use DATE function for more precise definition of 10 and 12 months - 300 is a bit dirty. nevertheless, it's up to OP to decide, the approach is absolutely right.

Nubtacular Over a year ago

Thank you Tim. I will get back to you within the hour to let you know how it turned out :)

Nubtacular Over a year ago

Nice! That seemed to work. I guess I'm confused on how it able to pick up the whole 'range' when you just enter the first of the range (in your example that would be B3). Also, when i add a new entry, it doesn't seem to pick up on the color (ie, enter 2/14/2013 it doesn't go to green/yellow/red).

Tim Williams Over a year ago

Make sure you select the full range before applying the CF

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related