VBA Converting a string (formatted in US Date) to UK date-CodePudding

I am trying to convert a string, which has a date in it but is US format into a UK date format. I have the following code which worked on another script I was writing but this seems to be hit a miss when it comes to a date code that is only single digits for both the day and the month it doesn't convert it correctly:

X = 3

Do While strTimeStamp = 0
    If InStr(WS2.Cells(lRow, lCol), "TIMESTAMP") <> 0 Then
        strHPCStats = Split(WS2.Cells(lRow, lCol), " ")
        'strHPCStats(X) = Mid(strHPCStats(X), 4, 6)
        re.Pattern = "^(\d{2})(\d{2})(\d{4})$"
        strHPCStats(X) = re.Replace(strHPCStats(X), "$3/$2/$1")
        strHPCStats(X) = Format$(strHPCStats(X), "dd/mmm/yyyy")
        strTimeStamp = strHPCStats(X)
        WS2.Cells(lRow, lCol).EntireRow.Delete
        lRow = lRow - 1
    Else
        WS2.Cells(lRow, lCol).EntireRow.Delete
        lRow = lRow - 1
        
    End If
    lRow = lRow   1
Loop

The typical string that is imported is:

4:19:17 (application) TIMESTAMP 3/13/2022

and the string where it is having trouble is:

5:36:32 (cameo) TIMESTAMP 4/1/2022

assuming it is something to do with the Reg Expression.

TYIA

CodePudding user response：

d{2} will look for exactly 2 digits, so if your date has a month (or day) with only 1 digit, the regex doesn't match.

If you want to specify 1 or 2 digits, you can for example use d{1,2}, so the statement would be

e.Pattern = "^(\d{1,2})(\d{1,2})(\d{4})$"

Details:

If, instead of just returning the date, you want to change the format within the string, you could do something like:

Sub convertStrings()
 Const d1 = "4:19:17 (application) TIMESTAMP 3/13/2022"
 Const d2 = "5:36:32 (cameo) TIMESTAMP 4/1/2022"
 
Dim sParts

sParts = Split(d1, " ")
sParts(UBound(sParts)) = Format(us2ukDate(sParts(UBound(sParts))), "dd-mmm-yyyy")
Debug.Print Join(sParts, " ")

sParts = Split(d2, " ")
sParts(UBound(sParts)) = Format(us2ukDate(sParts(UBound(sParts))), "dd-mmm-yyyy")
Debug.Print Join(sParts, " ")

End Sub