Conditional formatting date cells based on entry in adjacent cell

**koudesak** · 09-26-2011, 08:59 AM

Hello everyone!

Thanks in advance for helping me out. I've looked at similar posts but none of them were meeting my needs (apparently).

So...

In L2 I have a date and I want this cell to be green if the date is bigger than 30 days, yellow if between 0 to 29 days and red if in the past. This I can do! but...

I also want the cell to change color based on the choice I will plug from the pick list I've created in M2, which are green if received on time, yellow if received late and red if not received.

Basically I need the 1st conditional formatting to apply as long as M2 is blank but when M2 has data in it I want this to offset the color .

Here are the formulas I have in my cell now for the basic coloring based on the dates:

=L2-TODAY()>30 green
=L2-TODAY()>=1 yellow
=L2-TODAY()<=0 red

I really hope you guys will be able to help me enter that new parameter from M2 in my formula!

Thanks
Marco (Geneva, Switzerland)

**inayat** · 09-26-2011, 09:04 AM

Hi Marco,

Welcome to the forum...

Hope this helps

=and(M2="",L2-TODAY()>30)

**koudesak** · 09-26-2011, 10:43 AM

Hi inayat,

I've just tried but the cell L2 is still red, as if the value in that cell (5-Jan-11) and its conditional formating would not take in account M2.

The conditional formatting that is below should only apply when M2 is blank:

=L2-TODAY()>30 green
=L2-TODAY()>=1 yellow
=L2-TODAY()<=0 red

Then if not blank I want to add the following to the conditional formatting:

received on time = green
received late = yellow
not received = red

Not sure if that helps changing the perception of what could be the formula.

Many thanks!
Marco

**6StringJazzer** · 09-26-2011, 12:34 PM

You want to set up rules in this order, and check the box that says "Stop If True" for the first three rules.

Please Login or Register  to view this content.

**koudesak** · 09-27-2011, 04:16 AM

Hello 6StringJazzer,

Thanks a lot for the very detailed explanation, it works perfectly! I feel like I'm mastering excel now!

Really appreciate the help.

Kind regards,
Marco