4

In my exercise tracking spreadsheet, I want blank cells in columns B:O highlighted if column A is today's date, and the cell itself is blank. I have tried this:

=AND(ISBLANK(B3),$A:A=INT(NOW())) applied to range B3:O1000, but it doesn't highlight anything. Here's a sample sheet For the second term of the AND(), I've tried A:A=INT(NOW()),$A3=INT(NOW()), INT(NOW()), $A:$A=INT(NOW()) and A3=INT(NOW()), all with the same result: formatting not applied.

In column A, the formula =A:A=int(now()) does apply the intended format. If to the larger range, I just apply =ISBLANK(B3) without checking the date, all the blank cells are formatted. If I put the AND formula into a cell with references to today's date in column A and any blank cell, it returns TRUE. Confused!

OUCH!

JPV's answer solved the problem perfectly - yesterday. Today, with the new conditional formatting custom formula unchanged, it's back.

Blank cells in today's row aren't formatted in Safari (7.1.3 (9537.85.12.18), Chrome (Version 41.0.2272.89) or Firefox 36.0.1. I'm using a MacBook Air 13", OS X 10.9.5 4GB RAM, 1.4 GHz Intel Core i5.

I'm tempted to think Google has a server-side problem, because some of my earlier unsuccessful efforts did show the intended formatting on my iOS 7 iPhone - sometimes.

Can anyone explain or duplicate this problem?

2

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today()) 
  • Works like a charm! Thanks a million. Don't know why I didn't try $A3. – David Knapp Mar 15 '15 at 18:01

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.