More time questions and IF function problem
 

Please post response as email use is bogus to prevent spam. Thank you.

I am struggling with the following:

I want to have a cell that looks up the date value and put in the
correct value depending upon the date.

I have set the datevalue for certain months and compare them with the
=Now() in another cell that these will read off of.

Month one values are beginning 38504 end 38533 (other values based on
this) result in cell x6
Month two values are beginning 38534 and 38562 (other values based on
this) result in cell x7
My =Now() is in cell v1
If cell v1 is between 38504 and 38533 I want this cell value to be x6,
but if v1 if between 38534 and 38562, I want cell value to be x7.

Here is what I have come up with so far (and added month value in this
one) but it’s not working:
=IF(OR($V1=38504,$V1<=38533),$X6,OR($V1=38534,$V 1<=38562,X7,(OR($V1=38565,$V1<=38590,X8))))

I’d appreciate any help.

Thanks in advance.

baz

I think you confused "or" and "And" use and your formats were a little off.
(I think I have enough parenthesis
=IF(and($V1=38504,$V1<=38533),$X6,if(and($V1=385 34,$V1<=38562),X7,if(and($V1=38565,$V1<=38590),X8 ,"Other")))

"baz" wrote:

Please post response as email use is bogus to prevent spam. Thank you.

I am struggling with the following:

I want to have a cell that looks up the date value and put in the
correct value depending upon the date.

I have set the datevalue for certain months and compare them with the
=Now() in another cell that these will read off of.

Month one values are beginning 38504 end 38533 (other values based on
this) result in cell x6
Month two values are beginning 38534 and 38562 (other values based on
this) result in cell x7
My =Now() is in cell v1
If cell v1 is between 38504 and 38533 I want this cell value to be x6,
but if v1 if between 38534 and 38562, I want cell value to be x7.

Here is what I have come up with so far (and added month value in this
one) but its not working:
=IF(OR($V1=38504,$V1<=38533),$X6,OR($V1=38534,$V 1<=38562,X7,(OR($V1=38565,$V1<=38590,X8))))

Id appreciate any help.

Thanks in advance.

baz

I think this is what you mean

=IF(AND($V1=--"2005-06-01",$V1<=--"2005-06-30"),$X6,IF(AND($V1=--"2005-07-
01",$V1<=--"2005-07-29"),X7,IF(AND($V1=--"2005-08-01",$V1<=--"2005-08-26"),
X8,"")))


--
HTH

Bob Phillips

"baz" wrote in message
...
Please post response as email use is bogus to prevent spam. Thank you.

I am struggling with the following:

I want to have a cell that looks up the date value and put in the
correct value depending upon the date.

I have set the datevalue for certain months and compare them with the
=Now() in another cell that these will read off of.

Month one values are beginning 38504 end 38533 (other values based on
this) result in cell x6
Month two values are beginning 38534 and 38562 (other values based on
this) result in cell x7
My =Now() is in cell v1
If cell v1 is between 38504 and 38533 I want this cell value to be x6,
but if v1 if between 38534 and 38562, I want cell value to be x7.

Here is what I have come up with so far (and added month value in this
one) but it's not working:

=IF(OR($V1=38504,$V1<=38533),$X6,OR($V1=38534,$V 1<=38562,X7,(OR($V1=38565
,$V1<=38590,X8))))

I'd appreciate any help.

Thanks in advance.

baz

Bob.

THANK YOU!!! Works perfect. My regards to Burnham Beeches, Kettering,
Dunmow and a few other delightful towns in your country.

On Fri, 17 Jun 2005 19:19:37 +0100, "Bob Phillips"
wrote:

I think this is what you mean

=IF(AND($V1=--"2005-06-01",$V1<=--"2005-06-30"),$X6,IF(AND($V1=--"2005-07-
01",$V1<=--"2005-07-29"),X7,IF(AND($V1=--"2005-08-01",$V1<=--"2005-08-26"),
X8,"")))


--
HTH

Bob Phillips

"baz" wrote in message
...
Please post response as email use is bogus to prevent spam. Thank you.

I am struggling with the following:

I want to have a cell that looks up the date value and put in the
correct value depending upon the date.

I have set the datevalue for certain months and compare them with the
=Now() in another cell that these will read off of.

Month one values are beginning 38504 end 38533 (other values based on
this) result in cell x6
Month two values are beginning 38534 and 38562 (other values based on
this) result in cell x7
My =Now() is in cell v1
If cell v1 is between 38504 and 38533 I want this cell value to be x6,
but if v1 if between 38534 and 38562, I want cell value to be x7.

Here is what I have come up with so far (and added month value in this
one) but it's not working:

=IF(OR($V1=38504,$V1<=38533),$X6,OR($V1=38534,$ V1<=38562,X7,(OR($V1=38565
,$V1<=38590,X8))))

I'd appreciate any help.

Thanks in advance.

baz

An if statement can only evaluate one statement, not 3 as you are trying
to do here (you were also mis-placing some parentheses). Finally, I
think you want ANDs instead of ORs if you want to evaluate if numbers
fit between the values given. Using OR for the first statement will
return X6 in every case except when the value fall between the two
numbers. In order to handle the multiple statements, you need to use
nested IFs as follows:

=IF(AND($V1=38504,$V1<=38533),$X6,IF(AND($V1=385 34,$V1<=38562),$X7,IF(AND($V1=38565,$V1<=38590),$ X8,"None")))

Will return "None" if the value in V1 does not match any of the ranged
entered here. I think I've got it all, but did not test extensively.
Let me know if it doesn't work or if I'm misunderstanding - Chad


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380189

Do you know, I have never heard of Burnham Beeches or Dunmow (I do know
Kettering <vbg

Regards

Bob

"baz" wrote in message
...
Bob.

THANK YOU!!! Works perfect. My regards to Burnham Beeches, Kettering,
Dunmow and a few other delightful towns in your country.

On Fri, 17 Jun 2005 19:19:37 +0100, "Bob Phillips"
wrote:

I think this is what you mean


=IF(AND($V1=--"2005-06-01",$V1<=--"2005-06-30"),$X6,IF(AND($V1=--"2005-07
-

01",$V1<=--"2005-07-29"),X7,IF(AND($V1=--"2005-08-01",$V1<=--"2005-08-26")
,
X8,"")))


--
HTH

Bob Phillips

"baz" wrote in message
...
Please post response as email use is bogus to prevent spam. Thank you.

I am struggling with the following:

I want to have a cell that looks up the date value and put in the
correct value depending upon the date.

I have set the datevalue for certain months and compare them with the
=Now() in another cell that these will read off of.

Month one values are beginning 38504 end 38533 (other values based on
this) result in cell x6
Month two values are beginning 38534 and 38562 (other values based on
this) result in cell x7
My =Now() is in cell v1
If cell v1 is between 38504 and 38533 I want this cell value to be x6,
but if v1 if between 38534 and 38562, I want cell value to be x7.

Here is what I have come up with so far (and added month value in this
one) but it's not working:


=IF(OR($V1=38504,$V1<=38533),$X6,OR($V1=38534,$ V1<=38562,X7,(OR($V1=3856
5
,$V1<=38590,X8))))

I'd appreciate any help.

Thanks in advance.

baz

Chad:

Thank you. I will try this, it makes sense written out.

On Fri, 17 Jun 2005 13:43:52 -0500, cvolkert
wrote:


An if statement can only evaluate one statement, not 3 as you are trying
to do here (you were also mis-placing some parentheses). Finally, I
think you want ANDs instead of ORs if you want to evaluate if numbers
fit between the values given. Using OR for the first statement will
return X6 in every case except when the value fall between the two
numbers. In order to handle the multiple statements, you need to use
nested IFs as follows:

=IF(AND($V1=38504,$V1<=38533),$X6,IF(AND($V1=38 534,$V1<=38562),$X7,IF(AND($V1=38565,$V1<=38590), $X8,"None")))

Will return "None" if the value in V1 does not match any of the ranged
entered here. I think I've got it all, but did not test extensively.
Let me know if it doesn't work or if I'm misunderstanding - Chad


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380189