I am still new to VBA this problem is confusing me a bit. I am writing a macro that formats excel spreadsheets that I regurarely get.

The default date I have in this spreadsheet is saved as text. Changing it via UI and recording that as a macro fails to recognize the dates within the autofilter.

It looks something like

    Dim i As Integer
    i = Cells.Find(What:="*", _
                    After:=Range("C1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    Dim k As Integer
    
    For k = 3 To i
        Cells(k, 3) = Format(Cells(k, 3), "dd mmmm yyyy;@")
        ' Changing the format like dd.mm.yyyy;@ doesn't work at all 
    Next k

This does work partially but breaks for some reason on half the months like march, october, etc. When I double click the cell to edit it and hit enter the date gets saved correctly and is recognized by the autofilter.

How do I fix this?

Edit: Example of what it looks like inside the spreadsheet:

CodePudding user response：

Imagine the following texts in A1:A13 that we want to convert to real numeric dates

Option Explicit

Public Sub Example()
    Dim RangeToConvert As Range
    Set RangeToConvert = Range("A1:A13")  ' define the range of texts DD.MM.YYYY you want to convert to real numeric dates
    
    ' read them into an array for faster processing
    Dim Data() As Variant
    Data = RangeToConvert.Value2
    
    ' convert all texts to dates in that array
    Dim iRow As Long
    For iRow = LBound(Data, 1) To UBound(Data, 1)
        Data(iRow, 1) = ConvertTextDDMMYYYYtoDate(Data(iRow, 1))
    Next iRow
    
    ' write the real numerc dates back to the cells
    RangeToConvert.Value2 = Data
    
    ' format the date to whatever you like
    RangeToConvert.NumberFormat = "DD. MMM YYYY" 'however you want it to look like
End Sub


Public Function ConvertTextDDMMYYYYtoDate(ByVal DateString As String) As Date
    Dim Parts() As String
    Parts = Split(DateString, ".")  ' split date 13.01.2022 into 3 parts
    
    Dim RetVal As Date
    
    If UBound(Parts) = 2 Then ' check if there were 3 parts in the text if not it's the wrong format in the DateString 
        RetVal = DateSerial(Parts(2), Parts(1), Parts(0))  ' put the 3 parts together to a numeric date
        
        ' check if the numeric date is the same as the DateString we had as input
        If Format$(RetVal, "DD.MM.YYYY") <> DateString Then
            ' if that is not the case it means the string was no valid date and cannot be converted
            MsgBox """" & DateString & """ is not a valid date in the format TT.MM.JJJJ"
            Exit Function
        End If
    Else
        MsgBox """" & DateString & """ is not in the format TT.MM.JJJJ"
        Exit Function
    End If
    
    ' return the value as real numeric date
    ConvertTextDDMMYYYYtoDate = RetVal
End Function

And the outcome will be

Why did we do this If Format$(RetVal, "DD.MM.YYYY") <> DateString Then?
Because if your strings are "wrong" dates like 35.01.2022 we need to detect hat. Because DateSerial would just convert it to the date 04.02.2022 because day 35 does not exist in january.

Edit according comment

If you have multiple columns you need a second loop that loops through the columns too.

Dim iCol As Long
For iCol = LBound(Data, 2) To UBound(Data, 2)
    Dim iRow As Long
    For iRow = LBound(Data, 1) To UBound(Data, 1)
        Data(iRow, iCol ) = ConvertTextDDMMYYYYtoDate(Data(iRow, iCol ))
    Next iRow
Next iCol