Conditional formatting cells by due date usin tip054

[Lewis]

I have used the method in http://www.mrexcel.com/tip054.shtml
to set up cells showing due dates. However when a cell contains nothing it defaults to the first conditional format (red). Is there a way to use the last available conditional format statement to make empty cells colourless and any other date a third colour say yellow

 

[PaddyD]

mod for empty cell:

=and(len(e2),(E2-TODAY())<6)

change references to suit

paddy

 

[Lewis]

That does not seem to work still get the red background for empty cells

 

[PaddyD]

post the formulas for the conditional format(s) you currently have...

paddy

 

[Lewis]

They are exactly as in tip054

 

[PaddyD]

with cell e2 blank,

=AND(LEN(E2),(E2-TODAY())<6)

returns false, whereas

=(E2-TODAY())<6

returns true, because at today's date,

=E2-TODAY()

returns -37555. Reviewing the tip you mentioned, the suggestion solution should work, give that CF applies the format of the first true condition of the set...maybe check the formulas again, as CF has an odd habit of sticking quote marks round the formulas for no obvious reason.
paddy

 

[Lewis]

Works fine and leave me with the third condition which I set to =LEN(E2)>1 to give me a different colour for any other date