+ Reply to Thread
Results 1 to 6 of 6

Filtering a Date Range

  1. #1
    Leslie P via OfficeKB.com
    Guest

    Filtering a Date Range

    I have a spreadsheet that lists, with start dates in column M and end dates
    in column P. I would like to be able to automatically filter the rows if
    today's date falls inbetween the start and end dates. Can anyone clue me in
    as to how to do this?

    Thanks,
    Leslie

  2. #2
    Earl Kiosterud
    Guest

    Re: Filtering a Date Range

    Leslie,

    You can use an Autofilter. In column M, use Custom, "less than" (or "less
    than or equal to"), and use today's date. In column P, use "greater than"
    and today's date. You say you want this automatic. In that case, you'll
    need to record this in a macro, and have it pick up today's date. The code
    might look like

    Sub SetFilter()
    Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
    Operator:=xlAnd
    Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
    Operator:=xlAnd
    End Sub

    To reset the filter (show all), use this:

    Sub ResetFilter()
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=4
    End sub

    You'd need only paste this from here into a module, then make buttons (or
    something), and assign them to the macro (right click one, Assign macro).

    It would be a good idea to put the date on the sheet if you'll be printing
    this. The macro could do that too.
    --
    Earl Kiosterud
    www.smokeylake.com

    "Leslie P via OfficeKB.com" <[email protected]> wrote in message
    news:[email protected]...
    >I have a spreadsheet that lists, with start dates in column M and end dates
    > in column P. I would like to be able to automatically filter the rows if
    > today's date falls inbetween the start and end dates. Can anyone clue me
    > in
    > as to how to do this?
    >
    > Thanks,
    > Leslie




  3. #3
    Leslie P via OfficeKB.com
    Guest

    Re: Filtering a Date Range

    Thanks for your help, but when I tried it out, it ended up deleting all rows..
    .. Is there a specific format that my dates need to be in to go along with
    the Now() function?

    Earl Kiosterud wrote:
    >Leslie,
    >
    >You can use an Autofilter. In column M, use Custom, "less than" (or "less
    >than or equal to"), and use today's date. In column P, use "greater than"
    >and today's date. You say you want this automatic. In that case, you'll
    >need to record this in a macro, and have it pick up today's date. The code
    >might look like
    >
    >Sub SetFilter()
    > Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
    >Operator:=xlAnd
    > Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
    >Operator:=xlAnd
    >End Sub
    >
    >To reset the filter (show all), use this:
    >
    >Sub ResetFilter()
    > Selection.AutoFilter Field:=1
    > Selection.AutoFilter Field:=4
    >End sub
    >
    >You'd need only paste this from here into a module, then make buttons (or
    >something), and assign them to the macro (right click one, Assign macro).
    >
    >It would be a good idea to put the date on the sheet if you'll be printing
    >this. The macro could do that too.
    >--
    >Earl Kiosterud
    >www.smokeylake.com
    >
    >>I have a spreadsheet that lists, with start dates in column M and end dates
    >> in column P. I would like to be able to automatically filter the rows if

    >[quoted text clipped - 4 lines]
    >> Thanks,
    >> Leslie



    --
    Message posted via http://www.officekb.com

  4. #4
    Dave Peterson
    Guest

    Re: Filtering a Date Range

    Dates don't always play nice with autofilters. Sometimes, this'll work:

    Sub SetFilter()
    Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
    Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
    End Sub



    "Leslie P via OfficeKB.com" wrote:
    >
    > Thanks for your help, but when I tried it out, it ended up deleting all rows..
    > . Is there a specific format that my dates need to be in to go along with
    > the Now() function?
    >
    > Earl Kiosterud wrote:
    > >Leslie,
    > >
    > >You can use an Autofilter. In column M, use Custom, "less than" (or "less
    > >than or equal to"), and use today's date. In column P, use "greater than"
    > >and today's date. You say you want this automatic. In that case, you'll
    > >need to record this in a macro, and have it pick up today's date. The code
    > >might look like
    > >
    > >Sub SetFilter()
    > > Range("M1").AutoFilter Field:=1, Criteria1:="<=" & Now(),
    > >Operator:=xlAnd
    > > Range("M1").AutoFilter Field:=4, Criteria1:=">=" & Now(),
    > >Operator:=xlAnd
    > >End Sub
    > >
    > >To reset the filter (show all), use this:
    > >
    > >Sub ResetFilter()
    > > Selection.AutoFilter Field:=1
    > > Selection.AutoFilter Field:=4
    > >End sub
    > >
    > >You'd need only paste this from here into a module, then make buttons (or
    > >something), and assign them to the macro (right click one, Assign macro).
    > >
    > >It would be a good idea to put the date on the sheet if you'll be printing
    > >this. The macro could do that too.
    > >--
    > >Earl Kiosterud
    > >www.smokeylake.com
    > >
    > >>I have a spreadsheet that lists, with start dates in column M and end dates
    > >> in column P. I would like to be able to automatically filter the rows if

    > >[quoted text clipped - 4 lines]
    > >> Thanks,
    > >> Leslie

    >
    > --
    > Message posted via http://www.officekb.com


    --

    Dave Peterson

  5. #5
    Leslie P via OfficeKB.com
    Guest

    Re: Filtering a Date Range

    I got it to work, I had to change the fields to 13 and 16, my excel won't
    except it any other way. Thanks for all your help!!!

    Leslie

    Dave Peterson wrote:
    >Dates don't always play nice with autofilters. Sometimes, this'll work:
    >
    >Sub SetFilter()
    > Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
    > Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
    >End Sub
    >
    >> Thanks for your help, but when I tried it out, it ended up deleting all rows..
    >> . Is there a specific format that my dates need to be in to go along with

    >[quoted text clipped - 36 lines]
    >> >> Thanks,
    >> >> Leslie

    >



    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200508/1

  6. #6
    Dave Peterson
    Guest

    Re: Filtering a Date Range

    Ah, those field numbers start with the first column that's in the filtered
    range.

    So if you applied filters to A1:X999, then M would be field 13.

    "Leslie P via OfficeKB.com" wrote:
    >
    > I got it to work, I had to change the fields to 13 and 16, my excel won't
    > except it any other way. Thanks for all your help!!!
    >
    > Leslie
    >
    > Dave Peterson wrote:
    > >Dates don't always play nice with autofilters. Sometimes, this'll work:
    > >
    > >Sub SetFilter()
    > > Range("M1").AutoFilter Field:=1, Criteria1:="<=" & clng(date)
    > > Range("M1").AutoFilter Field:=4, Criteria1:=">=" & clng(date)
    > >End Sub
    > >
    > >> Thanks for your help, but when I tried it out, it ended up deleting all rows..
    > >> . Is there a specific format that my dates need to be in to go along with

    > >[quoted text clipped - 36 lines]
    > >> >> Thanks,
    > >> >> Leslie

    > >

    >
    > --
    > Message posted via OfficeKB.com
    > http://www.officekb.com/Uwe/Forums.a...mming/200508/1


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1