# Thread: if formula (XP)

1. ## 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

2. ## 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,

3. ## 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!!!

4. ## 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

5. ## Re: if formula (XP)

My "Brook" is short for Brooklyn, which is where I call home.

Glad I could be of some assistance.

6. ## 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.

7. ## 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.

8. ## Re: if formula (XP)

I did notice that your name is George - not Brook - still weird.

How are things in Brooklyn?

9. ## Re: if formula (XP)

Thank you, thank you, thank you!!!!

Linda

10. ## 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

11. ## 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 non-inclusive.

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

12. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•