Logical Formula Help

[dls61721]

I have a need for a working formula that has and OR and an AND statement.
All I can come up with is the following, but of course it does not work.
Thanks!
=IF(OR(C2#OR#D2#OR#E2,>4/1/2010#AND#<4/1/2013,1,""))

Doug

 

[Bernie Deitrick]

Doug,

=IF(SUMPRODUCT((C2:E2>DATEVALUE("4/1/2010"))*(C2:E2<DATEVALUE("4/1/2013")))>0,1,"")

HTH,
Bernie
MS Excel MVP

 

[Joe User]

I have a need for a working formula that has and OR and an AND statement. [....]
=IF(OR(C2#OR#D2#OR#E2,>4/1/2010#AND#<4/1/2013,1,""))

First, in Excel, OR and AND take the form of a function, like everything
else. So the general form of such an IF expression would be:

=IF(OR(AND(this,that),AND(this,that)), 1, "")

Second, however, this might be easier to express with a different function,
namely:

=IF(SUMPRODUCT((--"4/1/2010"<C2:E2)*(C2:E2<--"4/1/2013")), 1, "")

The multiplication ("*") is effectively an AND operation in this context,
and SUMPRODUCT is effectively an OR. The form --"4/1/2010" causes the date
string to be treated as a date number. Note: --"4/1/2010" is sufficient
for your own use. DATE(2010,4,1) would be better if others might use your
formulas, especially with different Regional and Language Options in the
Control Panel.

Note that this is equivalent to the following pseudo-expression (i.e. not as
you would write it in Excel):

IF((4/1/2010<C2 and C2<4/1/2013) or (4/1/2010<D2 and D2<4/1/2010) etc, 1,
"")

I assume that is what you wanted. If not, please be more specific.


----- original message -----

 

[Fred Smith]

The way you use And and Or is:
=if(or(and(c2>date(2010,4,1),c2<date(2013,4,1)),and(d2>date(2010,4,1),d2<date(2013,4,1)),and(e2>date(2010,4,1),e2<date(2013,4,1))))

Regards,
Fred

 

[dls61721]

Thank everyone for their assistance in this matter. It works fine and saved
me several hours of work
Doug