Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Use AutoFilter in Excel VBA to Filter by Date & Time

 

AutoFilter by Date and Time

Got any Excel Questions? Free Excel Help .

See Also Links Below if Not Familiar With AutoFilter in VBA

  1. Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds
  2. Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.
  3. Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates