Autofilter with date/other format

[Joannaj]

Dear all,

I hope you can help me.

I want to filter by date in a column, where the format is yyyy/mm/dd. But Excel recognizes this column as a String and not a date
-> Therefore the filter doesn't work (the filter comes from the sheet, and it is recongnized as date)

My VBA code (extract), with DateD0 and DateD7 as filter (i.e. 19/06/17 and 26/06/17)

DateD0 = CDate(wbMacro.Sheets(1).Cells(2, 5).Value)
DateD7 = CDate(wbMacro.Sheets(1).Cells(2, 7).Value)

With wbFinalWorkbook.Sheets(1)
.AutoFilterMode = False
.Range("A1:J1").AutoFilter
.Range("A1:J1").AutoFilter Field:=3, Criteria1:=">=" & DateD0, Operator:=xlAnd, Criteria2:="<=" & DateD7
End With

Even with DateD0 = wbMacro.Sheets(1).Cells(2, 5).Value, or with a new format for the raw data ( wbFinalWorkbook.Sheets(1).Range("C2", "C50000").NumberFormat = YYYY/MM/DD )
In all cases which I tried, Excel doesn't recognize the column as a date.

Has anyone an Idea, why it doesn't work?
Thanks a lot in advance!!
Cheers,
Joanna

[mana]

Please try this

DateD0 = wbMacro.Sheets(1).Cells(2, 5).Value2

[SamT]

Or maybe

DateD0 = Format(CDate(wbMacro.Sheets(1).Cells(2, 5).Value), "yyyy/mm/dd")

[Joannaj]

Hi,

Many thanks for your swift answer.
Unfortunately the filter does not work, the column C is still recognized as a string and not as a date and therefore the filters do not work. If I have a look in my excel sheet, I can see that the filters are well (as a date), but excel does not find any result with such filters.
I think the issue is in the column C of the raw data, which is not recognized by excel as a date column.

[Joannaj]

I found the solution:

1) I replace all value with a date in my rawdata
Dim rng As Range, cell As Range
Set rng = wbFinalWorkbook.Sheets(1).Range("C2", "C50000")
For Each cell In rng

If Not IsEmpty(cell.Value) Then
cell.Value = DateValue(cell.Value)
End If
Next cell


2) Instead of to write the date in the excel sheet, I calculate it in the VBA and therefor I use: .Range("A1:J1").AutoFilter Field:=3, Criteria1:=xlFilterLastWeek, Operator:=xlFilterDynamic

And it worked