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.

enter image description here

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.

  • is it because the dates after the first date in A3 are derived from formulas rather than actual dates? Jan 4, 2023 at 13:57

1 Answer 1


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

grey =$A3<today()

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

  • It would improve your answer if you explained why this works. Feb 4, 2023 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.