IF and dates

[PAL]

I have a multi row, multi column spreadsheet. Column E has a blank or a date
in each row. I am trying to write a formula that says IF there is a date AND
E2+5....

Using the greater than sign doesn't work because it must be looking for a
number. Pleas advise.


=IF((AND(E2>"",E2+5<$R$1)),"Yes","")

 

[John C]

You are right, it is looking for a number, as all dates are numbers, so why
don't you use E2>0 instead of E2>""?

 

[PCLIVE]

Maybe this:

=IF((AND(E2<>"",E2+5<$R$1)),"Yes","")

or

=IF((AND(E2>0,E2+5<$R$1)),"Yes","")

HTH,
Paul

 

[PAL]

I tried this, but a blank is not a "0"

You are right, it is looking for a number, as all dates are numbers, so why
don't you use E2>0 instead of E2>""?

 

[PCLIVE]

So what exactly is a "Blank"? Do you mean that the cell is empty...or is
there a space or some other non-visible character?

Regards,
Paul

--

 

[Pete_UK]

A blank is treated as 0 by Excel in formulae like this - perhaps you
don't have a completely empty cell, but maybe it contains one or more
spaces (or even character 160), so it might look blank to you.

Hope this helps.

Pete

 

[PAL]

Just got it like this,

=IF(E2="","",IF(AND(E2>0,E2+5<$R$1),"Yes",""))

Thanks.

 

[PCLIVE]

That seems to be a bit overkill. If the cell will either be blank or a
date, then you should just be able to check for the blank condition.

=IF(AND(E2<>"",E2+5<$R$1),"Yes","")

Regards,
Paul

--