Use AutoFilter in Excel VBA to Filter by Date & Time

AutoFilter by Date and Time

Got any Excel/VBA Questions? Free Excel Help.

See Also Links Below if Not Familiar With AutoFilter in VBA

AutoFilter by Date & Time With VBA

The 3 example codes below show how we can autofilter by date and time with VBA code.

Sub ByDateTimeGreater()

Dim dDate As Date, dTime As Date

Dim strDate As String

Dim lDate As Long, dblTime As Double

Dim dDateTime As Double



    dDate = DateSerial(2006, 9, 12)

    lDate = dDate

    

    dTime = TimeSerial(12, 8, 0)

    dblTime = dTime

    

    dDateTime = lDate + dblTime

    Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime

End Sub





Sub ByCellDateTimeGreater()

Dim dDate As Date, dTime As Date

Dim strDate As String

Dim lDate As Long, dblTime As Double

Dim dDateTime As Double

Dim rDate As Range, rTime As Range



Set rDate = Sheet1.Range("G1") 'Cell housing date & time



    If Not IsDate(rDate) Then 'Check if valid

      MsgBox "Non valid date and time in Sheet1 G1)"

      Exit Sub

    End If

    

    dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate))

    lDate = dDate

    

    Set rTime = rDate

    dTime = TimeSerial(Hour(rTime), Minute(rTime), Second(rTime))

    dblTime = dTime

    

    dDateTime = lDate + dblTime

    Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime

End Sub





Sub ByDateTimeEqual()

Dim dDate As Date, dTime As Date

Dim strDate As String

Dim lDate As Long, dblTime As Double

Dim dDateTime As Double



    dDate = DateSerial(2006, 9, 12)

    lDate = dDate

    

    dTime = TimeSerial(12, 8, 0)

    dblTime = dTime

    

    dDateTime = lDate + dblTime

    

    '= to does not work for non US date systems. So as with dates, we use

    'Greater than or Equal to and Less than or Equal to the SAME date & time.

	Range("A1").AutoFilter

    Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dDateTime, Operator:= _

        xlAnd, Criteria2:="<=" & dDateTime

End Sub

Gallery

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.