Joannaj
06-26-2017, 03:09 AM
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
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