CODEWITHSUNDEEP
excelregexvbastringdate
Andy M
Andy M

Reputation: 187

Converting a string with date in US format to UK format

I am trying to convert a string, which has a date in US format into UK format.

The following code seems to be hit or miss when it comes to a date that is single digits for both the day and the month:

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:

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

The string where it is having trouble:

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

Upvotes: 0

Views: 137

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

There's no need to use regular expressions, given your expected data.
Just look for two slashes in a space-separated string:

Function us2ukDate(S As String) As Date
    Dim v, w, x
    
v = Split(S, " ")
For Each w In v
    If (Len(w) - Len(Replace(w, "/", ""))) = 2 Then
        x = Split(w, "/")
        us2ukDate = DateSerial(x(2), x(0), x(1))
        Exit Function
    End If
Next w
    
End Function

testing example
enter image description here

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

enter image description here

Upvotes: 1

FunThomas
FunThomas

Reputation: 29296

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: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Upvotes: 1

Related Questions