Excel conditional format help.

Colbaker · 6 Sep 2015 at 00:30

Hi all,

Firstly imagine I have 3 columns of dates, cell A1 has start date in, B1 has requested end date, C1 has actual end date, what I am wanting to do is highlight cell C1 green if the date is on or before B1's date and C1 to turn red if the date entered is after the date entered in B1.

I assume it would be some sort of IF statement in the conditional format but can't seem to get it.....please help.

Scotteh · 6 Sep 2015 at 00:39

Cell Value greater than or equal to =B1 (make sure it's B1 not $B$1)

then when you copy it down it'll become B2 B3 B4 etc?

then of course make the formatting how you want it, then a cell value lower than =B1 and appropriate formatting.

If you wanted another column as a true/false sort of thing you could just have =IF(C1>=B1, "After", "Before") then do conditional formatting on "after" or "before" or whatever you wish them to be called.

hyperseven · 6 Sep 2015 at 01:14

Just set up 2 rules based on formula in conditional formating

obviously change for your <= bit

Colbaker · 6 Sep 2015 at 14:15

hyperseven said:
Just set up 2 rules based on formula in conditional formating

obviously change for your <= bit

That worked brilliantly thanks.

One last thing though, how do I get it so the cell stays blank if it is empty because at the moment it is green?

Scotteh · 6 Sep 2015 at 14:19

Put a rule in so if it equals blank then apply default format, make sure this is the highest rule (click up arrow on screenshot above)

So conditional format on screen above would be

Cell Value = ""