CODEWITHSUNDEEP
excel-formula
sethW
sethW

Reputation: 185

Conditional Highlighting with AND() function

I want a column to highlight cells only if it's older than a week and not blank. After reading through similar questions (apologies if this has been covered), I came to the below formula, but the blanks still get highlighted.

=AND(C5<=TODAY()-7,NOT(ISBLANK(C5)))

The date portion of the conditional works, but not the blank. I feel like I must be missing something very simple.

Thanks!!

Upvotes: 0

Views: 47

Answers (2)

Peter K.
Peter K.

Reputation: 960

Are you 100% sure that the cells are really blank? The ISBLANK formula is FALSE also if there is an empty text string, non-printing characters, etc. present in the cell.

Try with ISNUMBER instead (which will also be TRUE if it is a date) (I assume here that your cells only contain dates or blank cells):

=AND(C5<=TODAY()-7,ISNUMBER(C5))

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

IsBlank() will not return True if the cell is blank due to a formula. You can simply just use two double-quotes and it should work for you.

=AND(C5<=TODAY()-7,C5<>"")

Upvotes: 0

Related Questions