Google Sheets conditional formatting not working properly with date comparisons

Other Questions Google Sheets
1

I’m trying to set up conditional formatting in Google Sheets that changes cell colors based on dates but it’s not working as expected.

Here’s what I want to do: I have cells that are currently colored blue, and I want them to turn red automatically when a specific date arrives (like February 15th, 2024).

I’m using this formula for the conditional formatting:

=NOW()>=DATE(2024,2,15)

The problem is that even though today’s date is before February 15th, the formatting is still being applied right away instead of waiting for the correct date.

When I check the values:

  • NOW() gives me today’s date
  • DATE(2024,2,15) gives me the target date
  • The comparison NOW()>=DATE(2024,2,15) should return FALSE since we haven’t reached that date yet

But somehow the conditional formatting is still changing my cell colors immediately. What could be causing this issue with the date comparison in conditional formatting?

2

sounds like it could b a timezone issue. google sheets is a bit tricky with NOW() and zones! maybe switch to TODAY() since ur just lookin at dates, not exact times. also, double check that ur conditional formatting range is right, could be affecting the wrong cells.

3

Had this exact problem. Google Sheets gets weird with NOW() in conditional formatting because it includes time, not just date. Even when you’re comparing dates, the time part messes things up. Use TODAY() instead - it’s just the date. Try this formula: =TODAY()>=DATE(2024,2,15). Also check if you’ve got other formatting rules on those cells. I had an old rule that kept fighting with my new date rule. Clear all conditional formatting first, then add your rule back. That usually fixes it.

4

I’ve had the same frustrating issue with Google Sheets dates. The problem is usually timing - NOW() doesn’t always refresh when you expect it to in conditional formatting. Try TODAY() instead, and make sure your conditional formatting range uses absolute references ($A$1:$B$10). Go to Format > Conditional formatting to check this. What really helped me was forcing a refresh with Ctrl+Alt+Shift+F9 - sometimes the sheet just needs a kick to recognize today’s date. If it’s still acting up, create a helper column with your date formula first to test it before using it in conditional formatting.