I have a church rota Google Sheet. Column A shows the date of each service.

To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are colored differently. Ideally, the next upcoming date would be highlighted (or at least dates in the current month or something like this).

I am somewhat familiar with conditional formatting but not using it on dates - is this feasible and if so how can I do it? In a perfect world, the entire row would be colored for past weeks but just the cell is sufficient if this is getting tricky.

  • before today: =COUNTIF($A1,"<"&TODAY())
  • today: =COUNTIF($A1,"="&TODAY())
  • after today: =COUNTIF($A1,">"&TODAY())


  • 1
    That looks great - thanks for the demo! – Mr. Boy Apr 16 at 11:29
  • one follow-up - could I (easily) check if within N days of today i.e. today or in the next 2 weeks? – Mr. Boy Apr 16 at 11:30
  • 1
    sure: =COUNTIFS($A1,">"&TODAY(),$A1,"<"&TODAY()+10) – user0 Apr 16 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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