How to filter time when there is date

Question

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.

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

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"]

I believe you are going to need to add a helper column just with the time portion. — Scott Craner, Sep 09 '17 at 01:22
You can get just the time portion with this formula: `=MOD(A1,1)` — Scott Craner, Sep 09 '17 at 01:27
@ScottCraner - not sure that would work, since MOD() always returns a whole number. https://stackoverflow.com/questions/23549947/mod-with-doubles — Tim Williams, Sep 09 '17 at 04:00
your filter includes data between 3 PM and midnight inclusive — jsotola, Sep 09 '17 at 04:07
@ScottCraner - you are right: I was confusing the worksheet function with the VBA operator. — Tim Williams, Sep 09 '17 at 16:17

score 1 · Accepted Answer · answered Sep 09 '17 at 05:01

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:

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:

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

Feel free to upvote the answer if you feel it was useful. Glad to help — Robin Mackenzie, Sep 15 '17 at 23:09
hey quick question, so if I have multiple times is it possible to do it this way? and is there any way I can format the data then copy the visible data in a new row then filter that row instead? — barooon gara, Sep 15 '17 at 23:42
The `AutoFilter` only takes 2 criteria so if you wanted e.g. between 8am and 9am or between 2pm and 3pm, then this becomes difficult, perhaps not possible. I would look at reading the range into an array and processing the array with your custom logic then writing the array back to the sheet. — Robin Mackenzie, Sep 15 '17 at 23:45
Lets say 1st format the entire row to be time so there is no dates visible only times like hh:mm:ss AM/PM, then I copy the visible data, this way the days aren't copied only the times are copied then filter this new row between 9am-3pm or w.e. Is there any function to copy the visible cell data? — barooon gara, Sep 15 '17 at 23:50
Try following this doco: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel — Robin Mackenzie, Sep 15 '17 at 23:53

score 1 · Answer 2 · answered Sep 10 '17 at 15:24

1

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

answered Sep 10 '17 at 15:24

Docmarti

376
2
6

Thanks for the help. – barooon gara Sep 15 '17 at 08:52
hey quick question, so if I have multiple times is it possible to do it this way? and is there any way I can format the data then copy the visible data in a new row then filter that row instead? – barooon gara Sep 15 '17 at 23:41
You can certainly copy the visible cells, but it's a different problem. Please ask a new question. – Docmarti Sep 16 '17 at 07:11

How to filter time when there is date

2 Answers2