CF looking at more than 1 cell

J

Jock

With this CF in column "D", "Cell Value is less than =NOW()", the cell colour
becomes red if the date in "D" is less than today's date.
Column E may have text in it.
So using CF, "D" to go red if date is less than NOW() but if text in "E",
then remove red, i.e no formatting at all

Any ideas?
 
D

David Biddulph

Formula Is, rather than Cell Value Is
=AND(D1<TODAY(),NOT(ISTEXT(E1)))

If you want to use NOW() rather than today's date, you can change TODAY() in
the formula to NOW()
 
J

Jock

Nice one, thanks David.

The issue I now have is that the formula I have in "D" [=IF(C6="Y","",A6+3)]
treats A6 (which is empty) as 31/12/1900 with the result that all cells in
"D" (more or less) are red!
How do I get Excel to treat an empty cell as just that?
--
Traa Dy Liooar

Jock


David Biddulph said:
Formula Is, rather than Cell Value Is
=AND(D1<TODAY(),NOT(ISTEXT(E1)))

If you want to use NOW() rather than today's date, you can change TODAY() in
the formula to NOW()
 
D

David Biddulph

You could change your column D formula from
=IF(C6="Y","",A6+3)
to
=IF(OR(C6="Y",ISBLANK(A6)),"",A6+3) if A6 really is empty
or to
=IF(OR(C6="Y",A6=""),"",A6+3) if you want also to cover the cases where A6
might look empty because it contains ""

And I think you mean 31/12/1899, not 31/12/1900 ?
--
David Biddulph

Jock said:
Nice one, thanks David.

The issue I now have is that the formula I have in "D"
[=IF(C6="Y","",A6+3)]
treats A6 (which is empty) as 31/12/1900 with the result that all cells in
"D" (more or less) are red!
How do I get Excel to treat an empty cell as just that?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top