VBA filter not correctly filtering dates from date/time data

[Yamezz]

I have a report with dates and times in one column expressed as 2024-04-23T08:43:31+09:30
I have code that extracts the required date and time from this mess:

For Each cell In rReportTime
    cell = CDate(Left(WorksheetFunction.Substitute(cell.Value, "T", " "), 19))
Next

I also have code that determines a start and end date of a period of interest

StartReportDate = CDate(Left(PaymentReport.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Value, 10))
EndReportDate = CDate(Left(PaymentReport.ActiveSheet.Range("A2"), 10))

These lines correctly evaluate to 23/04/2024 and 29/04/2024 respectively

I then need to filter the data according to the start and end dates, but the following line of code returns a blank selection. If I manually filter the data, I can select dates, ignoring the time portion. Why can't the code do the same thing?

ExpReport.ActiveSheet.Range("A1", Cells(LastRow, LastCol)).AutoFilter Field:=3, Criteria1:=">=" & StartReportDate, Operator:=xlAnd, Criteria2:="<=" & EndReportDate

 

[Georgiboy]

Have you tried as below:

StartReportDate = CDbl(CDate(Left(PaymentReport.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Value, 10)))
EndReportDate = CDbl(CDate(Left(PaymentReport.ActiveSheet.Range("A2"), 10)))

 

[Yamezz]

Thanks Georgi, that gets me part way there. The filter is now applied from 23/04/2024 until 28/04/2024, despite EndReportDate correctly evaluating to 29/04/2024 and the filter criteria being ="<="

 

[Alex Blakenburg]

The 2 values you are using are the first 10 characters so it is just the date without the time. I assume you have declared the variables as dates.
You need to use CLng in your filter ie:

ExpReport.ActiveSheet.Range("A1", Cells(LastRow, LastCol)).AutoFilter Field:=3, Criteria1:=">=" & CLng(StartReportDate), Operator:=xlAnd, Criteria2:="<=" & CLng(EndReportDate)

 

[Georgiboy]

Not sure the time part would cause that but just as a test, you could swap the 'Cdbl' with 'Clng'

 

[Yamezz]

When I implemented your first reply I changed StartReportDate and EndReportDate to Double in the variable declaration. Should they be declared as double, long or date?

 

[Alex Blakenburg]

If you don't need the time then use Long and since you are only using the first 10 characters you are discarding the time.

 

[Georgiboy]

If you did it that way then Long

 

[Alex Blakenburg]

PS: Does your filter column (column 3) have date and time ?
If it does you are going to need to up the date by +1 and go with < (instead of <=)
ie
Criteria2:="<" & CLng(EndReportDate + 1)
(you can drop the CLng is you have changed the declaration to Long)

 

[Yamezz]

Hmm, trying different combinations of variable types and cDbl() or cLng() didn't change the issue.
After some head scratching, I realised my logic was off. 10:38am on the 29th is 45411.44, which is greater than 45411 (the beginning of the 29th), so to capture all times on the 29th I changed the criteria to ="<" EndReportDate +1