Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Filter dates between dates

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can you filter dates between to dates, either with auto filter or adv. Filter. I have a column with dates and would like to get the info between lets say 2-5-01 and 5-24-2001. Thanks

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use autofilter. Then, hit the dropdown arrow and choose Custom. You'll know where to go from there.


    ~Anne Troy

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-24 11:26, Paul B wrote:
    How can you filter dates between to dates, either with auto filter or adv. Filter. I have a column with dates and would like to get the info between lets say 2-5-01 and 5-24-2001. Thanks
    Lets say that A4:B14 houses what follows:

    {"date","item";
    36892,"i1";
    36893,"i2";
    36898,"i7";
    36900,"i9";
    36901,"i10";
    36925,"i3";
    36927,"i4";
    36930,"i5";
    36985,"i6";
    37005,"i8"}

    where the funny numbers are dates as they are internally represented.

    In A1:B2 enter:

    {"date","date";
    ">=2/5/2001",
    "<=5/24/2001"}

    without the double quotes, if you're wondering.

    Activate A5.
    Activate Data|Filter|Advanced Filter.
    Check 'Copy to another location'.
    Enter as 'List range', if Excel already did not so:

    $A$4:$B$14

    Enter as 'Criteria range':

    $A$1:$B$2

    Enter as value of 'Copy to', e.g.,

    $D$4:$E$4

    Click OK.

    I get in D4:E8 as result:

    {"date","item";
    36927,"i4";
    36930,"i5";
    36985,"i6";
    37005,"i8"}

    Aladin


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, I played around with it but can't get it to work, what part goes in A1 and what goes in B2?

    Dreamboat what would go in the custom auto filter?

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-24 13:29, Paul B wrote:
    Aladin, I played around with it but can't get it to work, what part goes in A1 and what goes in B2?

    Dreamboat what would go in the custom auto filter?

    In A1 enter: date
    In A2 enter: >=2/5/2001
    In B1 enter: date
    In B2 enter: <=5/24/2001

    Note that 'date' is a label from the sample data. I used for date criteria an inclusive between.

    Aladin

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    For the criteria, consider how your system is configured for dates.

    You may have to use for example
    >=5/2/01 or >=36927

    HTH

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That did it, I finally got it, thanks for the help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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