CODEWITHSUNDEEP
excelvbadatefilter
NoOneImportant
NoOneImportant

Reputation: 15

VBA - Filtering by Custom Date Format

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.

Upvotes: 0

Views: 267

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 1

Related Questions