Results 1 to 12 of 12
Thread: if formula (XP)

20021030, 17:29 #1
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
if formula (XP)
I need a formula in Column B that looks at Column A and if the dates in Column A are either between 10/1/02 and 10/31/02, or between 11/26/02 and 1/23/03, or between 2/26/03 and 4/3/03, or between 4/28/03 and 6/12/03 return a value of "NG" in column B if none of this is true, return "".
Thank you!!
Linda

20021030, 18:37 #2
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
I have attached a modified copy of your file with my solution. Basically I created an IF with an OR and a series of ANDs, and I entered the date ranges you mentioned in columns C and D. Otherwise you'd have to use something like the DATE function in the formula, making it more lengthy and more complex.
Hope this helps,

20021030, 18:43 #3
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
Perfect  I was trying to just use if(and(  forgot about the or.
Coincidence? My husband's name is Brook  how weird, huh?
Again  thank you, thank you, thank you!!!

20021030, 18:46 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: if formula (XP)
Try this in B2(all one line) and copy it down the column:
=IF(OR(AND(A2>DATEVALUE("10/1/02"),A2<DATEVALUE("10/31/02")),AND(A2>DATEVALUE("11/26/02"), A2<DATEVALUE("1/23/03")), AND(A2>DATEVALUE("2/26/03"),A2<DATEVALUE("4/3/03")), AND(A2>DATEVALUE("4/28/03"),A2<DATEVALUE("6/12/03"))),"NG","")
Steve

20021030, 18:52 #5
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
My "Brook" is short for Brooklyn, which is where I call home.
Glad I could be of some assistance.

20021030, 18:53 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
I think the formula below will do what you want:
<pre>=IF(ISNUMBER(A2),IF(OR(AND(A2>=DATEVALUE("1 0/1/02"),A2<DATEVALUE("11/1/02")),AND(A2>=
DATEVALUE("11/26/02"),A2<DATEVALUE("1/24/03")),AND(A2>=DATEVALUE("2/26/03"),A2<
DATEVALUE("4/4/03")),AND(A2>=DATEVALUE("4/28/03"),A2<DATEVALUE("6/12/03"))),"NG",""),"")
</pre>
Put that all on one line.Legare Coleman

20021030, 18:56 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
That's not going to work for 10/1/02, 11/26/02, 2/26/03, 4/28/03, and may not work for any of range end dates if the dates include a time.
Legare Coleman

20021030, 18:57 #8
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
I did notice that your name is George  not Brook  still weird.
How are things in Brooklyn?

20021030, 19:00 #9
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
Thank you, thank you, thank you!!!!
Linda

20021030, 19:01 #10
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
I'm going to have to get you a present you've helped me so many times.
Thank you again.
Linda

20021030, 20:25 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: if formula (XP)
I am not sure I understand. Are you referring to the fact that I did not use ">=" and "<=" but ">" and "<"?
They said between the dates so I made them noninclusive.
I don't think the "isnumber" will weed out any that the formula without it gets, so I chose not to check the appropriateness of the data. I try to simplify my convoluted formulas and it didn't seem necessary to check (I realize in VB this is more essential, but formulas are more fogiving about datatypes)
Steve

20021031, 01:34 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: if formula (XP)
Yes, I took the message to mean that it included the end dates. Now that I reread it, your interpretation could also be correct. I guess the originator can pick the solution that fits her need.
Legare Coleman