0

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
Community
  • 1
  • 1
Andy M
  • 167
  • 1
  • 3
  • 17
  • Does it work with 2 digits in month and day? – Nathan_Sav Jun 07 '22 at 08:55
  • it seems to work on the first string provided but just doesn't want to know on the second – Andy M Jun 07 '22 at 09:06
  • Does the same date come out? or do you get any errors? – Nathan_Sav Jun 07 '22 at 09:17
  • @Nathan_Sav it seems to be hit and miss and i really struggle to understand RegEx. when either the day OR month has 2 digits it seems to work as expected but when both the day and month are single digits it goes to s*** – Andy M Jun 07 '22 at 11:20

2 Answers2

1

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

FunThomas
  • 23,043
  • 3
  • 18
  • 34
1

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Cheers Ron for your answer. what does the Const do when adding a string ? – Andy M Jun 07 '22 at 15:15
  • 1
    @madmiddle Should be `Const d1 as String = "..."` in which case it declares `d1` as a String with a defined content. As is, it declares `d1` as a Variant, but for this small example, it's irrelevant. It is good practice to always declare your variables. You can enforce this by `Tools/Options/Editor/Code Settings` and selecting `Require Variable Declaration`. This will place `OPTION EXPLICIT` at the top of each inserted module. This will be extremely useful in catching typos, variables of the incorrect type, etc. – Ron Rosenfeld Jun 07 '22 at 15:53