To start off I am using google sheets.

I am looking for some help on this one as have been trying and searching the internet for different conditional formattings which aren't working.

I have 3 cells to highlight according to todays date or days prior to todays date.

For instance I have 2 cells on top row and 1 merged cell on row below.


A1 = calender date B1 = Person 1

A2-B2 merged = Person 2

I want all 3 cells to highlight together but I am only getting either the date cell to highlight or the non-date cells to highlight or all to highlight on the first condition (=today()) but them not revert back to normal or condition 2 the following day.

I have tried to condition using the 'date is...' option for today or yesterday or a week ago etc.

I have also tried to add the formula for conditon 1: =today()

For highlighting cells A1:B2 but it only highlights A1 with the date.

I have tried to add the formula for conditon 1: =IF(today(), true, false)

Then it highlights the 2 text cells but not the date cell.

I have tried a number of different things

including: =A1=today()

and again still doesn't work.

When I get them all to highlight together by some miracle they then don't revert or change to condition 2 the following day.

I also have the same issue with condition 2 for <today() either A1 highlights or only B1 and A2/B2.

I can't get my head around it.

I have a calendar that needs todays date highlighted green and any dates prior highlighted grey, I have been doing it manually for now but is annoying.

Please help.


So have just tried the following

Added =today() into cell A1 as not actually using that in my actual sheet

Selected H18:I19 and in condition 1 added formula

=H18=A1 and it worked

So I tried

=H18<A1 and that worked

Added condition 2 and then condition 1 would only highlight the date cell H18 again

Then tried the next set of cells J18:K19

=J18=A1 and it only highlights the date cell J18

=J18<A1 and it only highlights K18

=J18>A1 and it only highlights J19/K19 merged

What am I doing wrong?

  • 1
    Welcome to Web Applications. Please edit the question to show only the relevant information about the current state of the problem in order make clear and brief what is being asked. Also please add some screenshots to show how are you setting the conditional formatting rules. Also consider to add a link to a demo spreadsheet.
    – Rubén
    Dec 8, 2021 at 17:14


Your Answer

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

Browse other questions tagged or ask your own question.