VBA - Filtering by Custom Date Format

Question

I'm writing a macro to filter downloaded data according to a certain date range. The problem is that the cells containing dates in the downloaded data use the format mm/dd/yyyy, which Excel recognizes as text. Consequently, any program I write assumes that it is filtering text instead of dates, which causes a number of problems.

Is there a way to make this format recognized as a date by Excel? I tried defining the date cells using a custom number format but it didn't work. Any help would be appreciated.

Are all date cells in that specific range recognized as text? Is there a single such column? — FaneDuru
Yes. All the dates are lined up in a column and they are all recognized as text. I'm looking for a line/lines of code that would make Excel recognize the format mm/dd/yyyy as a date. I'm still new to VBA so I don't know if this is even possible. — NoOneImportant

FaneDuru FaneDuru · Accepted Answer · 2021-07-09T19:03:58

Please, try the next code. It will convert the column in discussion in Date:

Sub convertTextToDate()
  Dim sh As Worksheet, lastR As Long, col As String, arr, arrSp, arrD, i As Long
  
  Set sh = ActiveSheet
  col = "A" 'column where the string to be converted in date exists
    lastR = sh.cells(sh.rows.count, col).End(xlUp).row
  arr = sh.Range(col & "2:" & col & lastR).value
  ReDim arrD(1 To UBound(arr), 1 To 1)
  For i = 1 To UBound(arr)
        arrSp = Split(arr(i, 1), "/")
        arrD(i, 1) = DateSerial(CLng(arrSp(2)), CLng(arrSp(1)), CLng(arrSp(0)))
  Next i
  With sh.Range(col & "2:" & col & lastR)
        .value = arrD
        .NumberFormat = "dd/mm/yyyy"
  End With
End Sub

If you need a different format you should change "dd/mm/yyyy" with whatever you want. "mm.dd.yy", for instance...

VBA - Filtering by Custom Date Format

1 Answers