Conditional Formatting - with dates

[TG2812]

Hi,

I have the following table:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]Target[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Mar-18[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Sep-19[/TD]
[TD]Dec-20[/TD]
[/TR]
[TR]
[TD]My sales[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

In the conditional formatting i have entered "if cell in range C2:J2 is less than target cell ($B2) then color in blue" -> $E2<$C$2:$J$2

Any idea of to instruct Excel not to color the cell if dates in the header belong to the past (preferably to embed it in the above condition).

Thank you.

 

[Fluff]

How about
=AND(C2< $B2,C1 >TODAY())

 

[TG2812]

Hi Fluff,

Thank you very much for your help.
Can you please describe the steps to include your formula into the conditional formatting menu?

 

[Fluff]

Yup
Select C2:J2 (or c2:J & last row) > CF > New Rule > Use a formula > Enter the formula > Format > select your format > Ok > Ok.

If you have more than one row use
=AND(C2<$B2,C$1>TODAY())