IF and dates
 

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","")

IF and dates
 

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


"PAL" wrote:

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","")

IF and dates
 

Maybe this:

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

or

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

HTH,
Paul

--

"PAL" wrote in message
...
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","")

IF and dates
 

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

"John C" wrote:

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


"PAL" wrote:

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","")

IF and dates
 

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

--

"PAL" wrote in message
...
I tried this, but a blank is not a "0"

"John C" wrote:

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


"PAL" wrote:

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","")

IF and dates
 

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

On Jul 11, 4:48*pm, PAL wrote:
I tried this, but a blank is not a "0"



"John C" wrote:
You are right, it is looking for a number, as all dates are numbers, so why
don't you use E20 instead of E2""?
--
John C

"PAL" wrote:

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","")- Hide quoted text -

- Show quoted text -

IF and dates
 

Just got it like this,

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

Thanks.

"PCLIVE" wrote:

Maybe this:

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

or

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

HTH,
Paul

--

"PAL" wrote in message
...
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","")

IF and dates
 

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

--

"PAL" wrote in message
...
Just got it like this,

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

Thanks.

"PCLIVE" wrote:

Maybe this:

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

or

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

HTH,
Paul

--

"PAL" wrote in message
...
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","")