Why do conditional formatting rules based on date affect the wrong cells?

Question

Here is a Google Sheet for our church rota - publicly viewable: https://docs.google.com/spreadsheets/d/1KODl9N_I3VeO0CKBrU_75GoQ5dBvUuSzx0FinNKf9zs/edit?usp=sharing

I have conditional formatting rules applied to the whole range:

1. =countIF($A1,"<"&TODAY()) - set grey background for rows already happened
2. =COUNTIFS($A1,">"&TODAY(),$A1,"<"&TODAY()+10) - highlight the upcoming week or two

Yet today is January 4th 2023, and it is greying out the row Jan 15, and not rows above that. And the upcoming weeks are 8 & 15 Jan, yet the week 22nd Jan is the one being highlighted.

It was previously working but whenever people make edits, this sort of problem happens - it seems very fragile but worse, I can't even see why it's wrong. I can't see additional conditional formatting rules, it all looks right to me but I cannot fix it or see why it's not working as expected.

Answer 1

Try these rules (apply to range A3:C):

grey =$A3<today()

yellow =and($A3>=today(),$A3<today()+10)