Search code examples
google-sheetsgoogle-sheets-formulaconditional-formatting

How do I set conditional formatting based on data in two different cells?

I have a Google Sheet and I want to set a fill color in one cell based on a date and a text value in two different cells. Essentially, A1 should be red if B1 date is older than 15 days ago AND C1 reads "These are words" I have tried multiple things but I cannot seem to find any formulas that work. I have listed below what formulas I have tried along with what effect they had. Any help would be appreciated.

Additionally, I apologize if this has been answered somewhere, I looked but could not find anything.

Only highlights date that is EXACTLY 15 days ago =AND($B:$B=TODAY()-15,$C:$C="These Are Words")

Does not Highlight anything =AND(=ISBETWEEN($B:$B=TODAY()-15,=TODAY(),$C:$C="These Are Word")

I have also tried setting specific cells (i.e. B1 instead of $B:$B)

Example of desired effect

Solution

  • Instead of the = operator use the <= operator:

    =AND($B:$B<=(TODAY()-15),$C:$C="These Are Words")