Filtering between two Dates in Different Years

[NorthbyNorthwest]

Hi, everyone. I have a dataset that I want a user to be able to enter a start date in a worksheet cell and end date in another cell, filter by those parameters, and copy filtered data to another worksheet with VBA. Problem is my work adheres to a fiscal calendar--October being the first month and September the last. This means the user will often have to ask for dates across two calendar years. Example: October 1, 2017 to February 28, 2018. I cannot figure out how to do this in code. I have been researching and every example I find is for a calendar year. Does anyone know how this can be accomplished?

 

[JLGWhiz]

Excel sees the date as an integer number counting from 1/1/1900 as 1 until the current date 43056. So different years would not matter. You can filter it using the operator xlAnd. Criteria:=">=10/1/2017", xlAnd, "<=2/28/2017"

 

[NorthbyNorthwest]

Thanks JLGWhiz for responding to my post. After further research I found answer in apost by Tom Urtis https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-filtering-dates/,in which he used the DateSerial function tofilter dates. I did know, as you explained in above, that Excel sees dates as a number count, starting with 1 on 1/1/1900. However, I thought if I formatted my dates as Dates that would be sufficient to get correct filtering results. For whatever reason, I didn't. work. I wound up following Mr. Urtis's example, and it works like a charm. I also found help in setting up a worksheet filter for my users ina post by Trevor Easton Auto Filter Between Dates - Excel VBA - Online PC Learning. See end result.

Sub ShowAll()
'show all filtered data and remove filter
    With Sheet23
        IfSheet23.AutoFilterMode Then
           Sheet23.Range("B7").AutoFilter
        End If
    End With
End Sub
 
Sub ShowAllRecords()
'show data and keep filter
    IfSheet23.FilterMode Then
       Sheet23.ShowAllData
    End If
'copy the filtered data
    CopyFilter
End Sub
 
Sub CopyFilter()
'clear the contents
   Sheet30.Range("B7:CK3007").ClearContents
'copy and paste the range
   Sheet23.Range("Database").SpecialCells(xlCellTypeVisible).Copy_
   Destination:=Sheet30.Range("B7")
End Sub
 
Sub Between2Dates()
'Sources:  TrevorEaston
'http://www.onlinepclearning.com/auto-filter-dates-excel-vba/
'and Tom Urtis
'https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-filtering-dates/
 
Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim FilterRange As Range
 
'Set error handler
On Error GoTo errHandler:
 
'Stop screen flicker
Application.ScreenUpdating = False
 
'Set the variables
StartDate = Sheet30.Range("G2").Value
EndDate = Sheet30.Range("H2").Value
 
 
'Check the dates if OK to run the filter
 IfSheet30.Range("G2").Value >= Sheet30.Range("H2").ValueThen
    MsgBox "Daterange invalid.  Try again."
 Exit Sub
 
 Else
 If NotIsEmpty(StartDate) And Not IsEmpty(EndDate) Then
 
Set FilterRange = Sheet23.Range("B7")
FilterStartDate = DateSerial(Year(StartDate),Month(StartDate), Day(StartDate))
FilterEndDate = DateSerial(Year(EndDate), Month(EndDate),Day(EndDate))
 
'Run filter
    With FilterRange
        .AutoFilterField:=1, Criteria1:=">=" & CDbl(FilterStartDate), _
       Operator:=xlAnd, Criteria2:="<=" & CDbl(FilterEndDate)
     
        'Copy values
        CallCopyFilter
        
        'Show all data
        Call ShowAll
    End With
 End If
 End If
 
'Error block
On Error GoTo 0
Exit Sub
 
errHandler:
MsgBox "There is no data"
 
Call ShowAllRecords
Sheet30.Select
End Sub