Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Filter Data Between Two Dates

  1. #1
    Join Date
    18th August 2006
    Posts
    19

    Filter Data Between Two Dates

    hi, i found this code somewhere. this code was supposed to filter data for a period of 30 days.
    my case is that i have 2 strings of dates. in the dd-mmm-yy format. both of the dates can be any day. how do i modify this code to suit my case?

    Code:
    Filtermonth Format(Sheets("Reference").Cells(ComboBox1.ListIndex + 2, 19), "dd-mmm-yyyy"), _
    Format(Sheets("Reference").Cells(ComboBox6.ListIndex + 32, 19), "dd-mmm-yyyy")
    and the support code for the "FilterMonthSolvent" is as below:
    Code:
    Sub FilterMonthSolvent(strCriteria3 As String, strCriteria4 As String)
        With Sheet2
            .AutoFilterMode = False
            .Range("A1:O1").AutoFilter
            .Range("A1:O1").AutoFilter Field:=3, Criteria1:=">=" & strCriteria3, _
            Operator:=xlAnd, Criteria2:="<=" & strCriteria4
        End With
    End Sub

    thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: Filter Data Between Two Dates

    The code will still do this for (ie the sub itself, your second code example).

    The first example is where the code is called from and this is the bit you'll need to amend. All you need to do is pass the dates in questions...so the call would be
    Code:
    Filtermonth StartDate, EndDate
    D

    Better a bad day on the water than a good day in the office

  3. #3
    Join Date
    18th August 2006
    Posts
    19

    Re: Filter Data Between Two Dates

    thanks for your prompt reply. how do i define the "startdate" and "end date"? actually my date is take from 3 combo boxes. which one is for year, one is for month, another one is for day. after that i concentrate them together into one string, as below:
    Code:
    Sub Concentrate()
    Dim ConcDate1 As String
    ConcDate1 = ComboBox5.Text & "-" & ComboBox4.Text & "-" & ComboBox3.Text
    End Sub
    how do i call them? in the function "FilterMonth"?

    thanks again.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: Filter Data Between Two Dates

    Using that sub - you would have to make ConcDate1 a global variable so that it can be used outside of the routine itself (see VBE help on Understanding the Lifetime of Variables). It is also possible to dim this as a date instead of a string.

    I presume you have a ub for both the start and end date though...

    The code in my last post shows how you would call the function itself
    D

    Better a bad day on the water than a good day in the office

  5. #5
    Join Date
    18th August 2006
    Posts
    19

    Re: Filter Data Between Two Dates

    erm..sorry.. i still don't quite understand. first, i tried it in this way:
    Code:
    Private Sub OkButton_Click()
    If ComboBox8.Value = True Then
    FilterMonth StartDate, EndDate
    end if
    End Sub
    
    'the code to filter is as the sub i mentioned above
    
    Sub Concentrate()
    Dim ConcDate1 As Date
    ConcDate1 = ComboBox5.Text & "-" & ComboBox4.Text & "-" & ComboBox3.Text
    End Sub
    
    Sub Concentrate1()
    Dim ConcDate As Date
    ConcDate = ComboBox8.Text & "-" & ComboBox7.Text & "-" & ComboBox6.Text
    End Sub
    
    Sub StartDate()
    Dim StartDate As Date
    Call Concentrate
    StartDate = ConcDate1
    End Sub
    
    Sub EndDate()
    Dim EndDate As Date
    Call Concentrate1
    EndDate = ConcDate
    End Sub
    In the end, it still doesn't work.
    To my knowledge and to my case, for the function "FilterMonth", we have to assign the location(the exact cell) of startdate and enddate for the function to filter. isn't it?
    For my code below, the two variables for the function to filter are two columns of dates of a year (365 days) .
    if not, how to filter the date not according to their location but the date itself?

    Code:
    FilterWeek Format(Sheets("ExtractedData").Cells(ComboBox9.ListIndex + 2, 1)), _
    Format(Sheets("ExtractedData").Cells(ComboBox10.ListIndex + 2, 1))
    thanks for helping me.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: Filter Data Between Two Dates

    I would try something along these lines...if this doesn't point you in the right direction - are you able to post an example of the workbook to have a look at ??
    Code:
    Sub FilterMonthSolvent(strCriteria3 As String, strCriteria4 As String)
        
        'This will apply an autofilter on columns A:0 on Sheet2
        'Then on the third column of the list
        'filter between the dates selected
        
        With Sheet2
            .AutoFilterMode = False
            .Range("A1:O1").AutoFilter
            .Range("A1:O1").AutoFilter Field:=3, Criteria1:=">=" & strCriteria3, _
            Operator:=xlAnd, Criteria2:="<=" & strCriteria4
        End With
    End Sub
    
    Sub testit()
    Dim dStartDate As Date, dEndDate As Date
    
    If combobox8.Value = True Then
        dStartDate = DateSerial(combobox5.Value, combobox4.Value, combobox3.Value)
            'These would be in Year, Month, Day Sequence
            'and the values are required to be numeric
            'you could also take the listindex of the combobox
        dEndDate = DateSerial(combobox8.Value, combobox7.Value, combobox6.Value)
            'See notes above
        FilterMonthSolvent dStartDate, dEndDate
    End If
           
    End Sub
    D

    Better a bad day on the water than a good day in the office

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Advanced Filter Between Dates
    By tsrwebman in forum EXCEL HELP
    Replies: 9
    Last Post: February 28th, 2014, 15:20
  2. Filter Dates Via UserForm
    By helpme123 in forum EXCEL HELP
    Replies: 5
    Last Post: August 7th, 2008, 09:12
  3. Allow User To Filter Between 2 Dates
    By Excel Chick in forum EXCEL HELP
    Replies: 1
    Last Post: February 7th, 2008, 17:50
  4. Filter On Dates
    By nayone in forum EXCEL HELP
    Replies: 6
    Last Post: July 11th, 2007, 22:42
  5. Filter By Dates
    By albatros81 in forum EXCEL HELP
    Replies: 5
    Last Post: June 25th, 2007, 21:40

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
  •  
porno