CODEWITHSUNDEEP
excelexcel-formula
BamAlmighty
BamAlmighty

Reputation: 73

Excel conditional formatting with blanks and Today()

I am trying to get a cell to highlight, given 2 criteria. The cell must be blank, and today's date must be after a predetermined date that is listed in another cell(R8C3). I'm also using R1C1 notation, but understand A1 notation as well. The part I don't understand is that each criteria works independently, but when I use the AND function it no longer works. The relative references are used since this formatting will span multiple columns.

=ISBLANK(RC) evaluates to true and highlights correctly.

=TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC))) evaluates to true and highlights appropriately.

=AND(ISBLANK(RC),TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC)))) Does not highlight.

A slightly modified version pasted in a cell ( to check that the formula works) evaluates appropriately to TRUE or FALSE. =AND(ISBLANK(RC3),TODAY()>=INDIRECT(ADDRESS(ROW(R8C3),COLUMN(RC3))))

I'm using Excel 2010 (version 14) on a Windows 7 device.

Upvotes: 3

Views: 1275

Answers (2)

Roger H.
Roger H.

Reputation: 336

Try this:

=(J$1>K$1)*(ISBLANK(A1))

where J1 has the current date and K1 the threshold date.

Upvotes: 1

Michael
Michael

Reputation: 4848

The indirect statement seems unnecessarily convoluted... why not just use a mixed reference?

=AND(ISBLANK(RC), TODAY()>=R8C)

Upvotes: 0

Related Questions