Can I use "OR" in two auto-filters/custom filters?

Discussion in 'Microsoft Excel Misc' started by Roady, May 24, 2010.

  1. Roady

    Roady Guest

    Hi!

    Columns A and B both have dates entered. Col A is a start date and Col B is
    an end date.

    I want the user to filter using a combination of the two. For example...
    I want to show all lines of data that have dates that fall between 1/1/10
    and 5/5/10. That could mean that the start date or the end date falls within
    this range. So even if Col A starts at 12/20/09, but in Col B, it is ends on
    1/15/10, it should still show this line of data.

    can you help with a formula or filter to achieve this combo? I think it
    would be an "OR" statement of some kind possibly since it doesn't need to
    meet both Col criteria, just 1 in order to be shown.

    Thanks! R
     
    Roady, May 24, 2010
    #1
    1. Advertisements

  2. Roady

    Luke M Guest

    Can't do it with only the AutoFilter...

    Lets say you put your start/stop dates in Y1 and Z1 respectively.
    Formula:
    =OR(AND(A2>=Y$1,A2<=Z$1),AND(B2>=Y$1,B2<=Z$1))
    Coyp down as needed, then AutoFilter for TRUE on this column.

    --
    Best Regards,

    Luke M
    "Roady" <> wrote in message
    news:...
    > Hi!
    >
    > Columns A and B both have dates entered. Col A is a start date and Col B
    > is
    > an end date.
    >
    > I want the user to filter using a combination of the two. For example...
    > I want to show all lines of data that have dates that fall between 1/1/10
    > and 5/5/10. That could mean that the start date or the end date falls
    > within
    > this range. So even if Col A starts at 12/20/09, but in Col B, it is ends
    > on
    > 1/15/10, it should still show this line of data.
    >
    > can you help with a formula or filter to achieve this combo? I think it
    > would be an "OR" statement of some kind possibly since it doesn't need to
    > meet both Col criteria, just 1 in order to be shown.
    >
    > Thanks! R
     
    Luke M, May 24, 2010
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.