More time questions and IF function problem

B

baz

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
 
B

bj

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

Bob Phillips

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

baz

Bob.

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

cvolkert

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

=IF(AND($V1>=38504,$V1<=38533),$X6,IF(AND($V1>=38534,$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 range
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 - Cha
 
B

Bob Phillips

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

Regards

Bob
 
Top