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.


1 Answer 1

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


  • 1
    That looks great - thanks for the demo!
    – Mr. Boy
    Commented Apr 16, 2019 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
    Commented Apr 16, 2019 at 11:30
  • 1
    sure: =COUNTIFS($A1,">"&TODAY(),$A1,"<"&TODAY()+10)
    – user0
    Commented Apr 16, 2019 at 11:32

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.