Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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?

    VB:
    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:
    VB:
    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
    VB:
    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:
    VB:
    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:
    VB:
    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?

    VB:
    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 ??
    VB:
    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