Dynamic Date and Time Filtering

[Grant A]

Hi All,

I have working teams that record data with date and time stamps and am after some assistance with the filtering of this data.

I have two questions which there is attached file for sample data

Question 1 - On sheet 1 enter start date in cell "F5" and finish dates in cell "F7"
this will filter sheet 2 in column C between the dates.
i have the following code but this is only working if the start - finish cells are on same worksheet. I wish to have the date filters on other sheet

PublicSub MyFilter()

Dim lngStart AsLong, lngEnd AsLong
lngStart = Range("B3").Value 'assume this is the start date
lngEnd = Range("C3").Value 'assume this is the end date

'assume you have field name / label in C1
Range("C2:C300").AutoFilter field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd

EndSub

Question 2 - from same date data on sheet 2 column C date and time, am after a code to filter the previous shift events. Our business functions 24 hours with events recorded with time and date stamps. Would request assistance to have filter with starting from now() minus 12 hours to filter down the events in the previous shift.

Regards
GrantA
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[vletm]

Something like these (without any error handling)

Sub Macro1()
    lngStart = Format(Sheets("sheet1").Range("F5"), "0.0000000000000000000")
    lngEnd = Format(Sheets("sheet1").Range("F7"), "0.0000000000000000000")
    Sheets("sheet2").Range("$C$2:$C$200").AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
End Sub

Sub Macro2()
    lngStart = Format(Now, "0.0000000000000000000")
    lngEnd = Format(Now - 0.5, "0.0000000000000000000")
    Sheets("sheet2").Range("$C$2:$C$200").AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
End Sub

 

[Grant A]

vletm,

thank you for the update and apology for lag in reply.
will give these trial this morning

greatly appreciate your help once again

 

[Grant A]

Vletm,

once again great help. Both codes have worked and i have been able to implement one already into program which we had functional this evening.

The second needs bit more work. Just confirming the sort and filter order of the data but from the raw test the code worked to requirements

With Macro 2 needed to interchange the between dates as the lngEnd was before the now time of lngStart and this functioned to filter between the two times.

again, thank you
Grant