Conditional formatting with date comparison in Google Sheets

0
votes

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:

  1. IF this date is greater than or equal to (i.e. later) the date at A1 --> Background is yellow.
  2. IF this date is less than (i.e. earlier) the date at A1 --> Background is green.

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!

1
excelgoogle-sheetsgs-conditional-formatting
@pnuts Because this could apply to an excel formula as well (afaik the syntax is the same). – Macklin

1 Answers

1
votes

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.

enter image description here