I'm rather stuck on what should be a simple formula for a couple of conditional formatting rules in Google Sheets. My data consists of dates in the format DD/MM/YYYY
(as set in Format->Number->Date). I've got a reference date "Last Updated" at A1
, and a column of dates at the range B1:B9
. Both of my rules are applied to this range, and should work like the following:
I've tried a number of different formulas, but none seem to work properly or as expected. For example, with the first conditional formatting rule:
=$B1 >= A1
=GTE($B1, A1)
...and again with VALUE($B1)
, DATEVALUE($B1)
Here are the results of the first rule with a custom formula =(VALUE($B1) >= VALUE(A1))
. It doesn't look too accurate to me.
-- A ---------- B ---------- Expected ---------- Actual ----------
1 02/02/2015 01/01/2015 FALSE FALSE
2 02/01/2015 FALSE TRUE (YELLOW)
3 03/01/2015 FALSE TRUE (YELLOW)
4 01/02/2015 FALSE TRUE (YELLOW)
5 02/02/2015 TRUE (YELLOW) TRUE (YELLOW)
6 03/02/2015 TRUE (YELLOW) FALSE
7 01/03/2015 TRUE (YELLOW) FALSE
8 02/03/2015 TRUE (YELLOW) FALSE
9 03/03/2015 TRUE (YELLOW) TRUE (YELLOW)
I'm hoping that I'm just overlooking something simple, could someone point me in the right direction?
Thanks!
You just need to set the dollar signs on A1. Otherwise as you copy the conditional formatting formula down, it will change to =$B2>=A2, etc., so you will be comparing B2 with an empty cell (or whatever happens to be in A2, A3 etc. in your sheet):-
=B1>=$A$1
Of course it doesn't do any harm to put the dollar sign in front of the B as well, but not necessary if you're just formatting one column.