Results 1 to 12 of 12

Thread: if formula (XP)

  1. #1
    5 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    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,
    Attached Files Attached Files

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    2 Star Lounger
    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.

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

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

  8. #8
    5 Star Lounger
    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?

  9. #9
    5 Star Lounger
    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

  10. #10
    5 Star Lounger
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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 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. #12
    Uranium Lounger
    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

Posting Permissions

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