CODEWITHSUNDEEP
excelvba
barooon gara
barooon gara

Reputation: 123

How to filter time when there is date

I know how to filter just time in a column, but how is it done if date is also in the same column?

I tried to filter by number between 12:00:00 AM - 3:00:00 PM but it doesn't filter at all.

The data was formatted like this.

enter image description here

I changed it to show only the time and not the date.

enter image description here

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.Range("A4", "A" & lastRow).NumberFormat = "hh:mm:ss AM/PM"

ActiveSheet.Range("$A$4:$C$18").AutoFilter Field:=1, Criteria1:="<=m/d/yyyy 12:00:00 AM", Operator:=xlAnd, Criteria2:=">= m/d/yyyy 3:00:00 PM"

ActiveSheet.Range("$A$4:$C$18").AutoFilter Field:=1, Criteria1:="<=12:00:00 AM", Operator:=xlAnd, Criteria2:=">=3:00:00 PM"]

Upvotes: 1

Views: 2598

Answers (2)

Docmarti
Docmarti

Reputation: 386

Sub Original_01()
Dim Criteria1 As Variant
Dim Criteria2 As Variant
ActiveSheet.AutoFilterMode = False
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1" & ":C" & lastRow)
dtConstant = DateValue(CDate(r.Cells(2, 1).Value))
Criteria1 = CDbl(dtConstant + TimeValue(CDate("04:00:00 AM")))
Criteria2 = CDbl(dtConstant + TimeValue(CDate("08:30:00 AM")))
Criteria1 = ">=" & Replace(Criteria1, ",", ".")
Criteria2 = "<=" & Replace(Criteria2, ",", ".")
r.AutoFilter Field:=1, Criteria1:=Criteria1, Criteria2:=Criteria2
End Sub

Upvotes: 1

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Assuming the data has a constant date (which in your example I guess is 6/1/2017) you can use the approach below which takes the date from the first data point and then builds the criteria from the sum of the date part plus whatever inputs you want to pass in for the time part.

The line:

dtCriteria1 = CDate(CLng(dtConstant)) + TimeValue("00:03:00")

Converts that first data point to a date only by retaining the integer portion of the number representing the date, then adds on the time value of the hours, minutes and seconds part.

So, starting with this:

enter image description here

Run this code:

Option Explicit

Sub FilterTimeWithADate()

    Dim ws As Worksheet
    Dim rng As Range
    Dim dtConstant As Date
    Dim dtCriteria1 As Date
    Dim dtCriteria2 As Date

    ' get references to sheet and range
    Set ws = ThisWorkbook.Worksheets("Sheet1") '<-- set to your worksheet
    Set rng = ws.Range("A1:A24") '<-- set to your range

    ' reset filter
    ws.AutoFilterMode = False

    ' reset range numberformat
    rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1).NumberFormat = "dd/mm/yy h:mm:ss"

    ' get date from first data point
    dtConstant = CDate(rng.Cells(2, 1).Value)

    ' set filter dates
    dtCriteria1 = CDate(CLng(dtConstant)) + TimeValue("00:03:00")
    dtCriteria2 = CDate(CLng(dtConstant)) + TimeValue("00:08:30")

    ' set range format
    rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1).NumberFormat = "hh:mm:ss AM/PM"

    ' set filter
    rng.AutoFilter Field:=1, _
        Criteria1:=">=" & dtCriteria1, _
        Operator:=xlAnd, _
        Criteria2:="<=" & dtCriteria2

End Sub

To end up with this:

enter image description here

You might want to step through the code in debug mode to see the behavior at each point.

Upvotes: 1

Related Questions