Edate formula for blank cells is now making my conditional format colour not work

[joz110820]

Not sure that header makes sense...

So I have an edate formula in collumn N, I was getting #Value whenever I Ctrl D down on blank cells so I applied formula: =IF(M3="","",EDATE(M3,36)) which worked well. HOWEVER, I now can't apply my conditional format where I want it to highlight cells less than today's date red, it usually highlights the blank ones as well (which is what i need) but I think the formula has stopped that. Gosh I hope this makes sense, it's been a looong day.

 

[Fluff]

What is the formula in the conditional formatting?

 

[joz110820]

What is the formula in the conditional formatting?

I've just been going in to Conditional Formatting, Highlight Cell Rules, Less Than... then I type =today() and mark it red.

 

[Fluff]

In that case you could use

=OR(N2="",N2<=TODAY())

 

[joz110820]

Morning Fluff, Thanks for that. There is already the following formula in the cell: =IF(M2="","",EDATE(M2,36)) so how would I incorporate your suggested on in to that? Sorry, I should have noted that in my last message to you, I only mentioned the conditional format I have on it.

 

[Fluff]

The formula I suggested is for the conditional format rule, not for the cell.

 

[joz110820]

Thank you for your patience with me. I tried pasting that formula in to the conditional format and it made all the column, red.

 

[Fluff]

You need to select New Rule, Use a Formula & then paste the formula into the formula box

 

[joz110820]

That hasn't worked. I've gone back to the beginning, adding the edate formula which throw's the #value up when there are blank cells, perhaps there is an alternative way for me sort out the blanks as I think the formula I used is what is stopping me from applying the conditional format.

 

[Fluff]

That hasn't worked.

In what way?