Reverse Date-string "yyyymmdd" to "ddmmyyyy" using loops only

[Denin Srmic]

Hello,
I have tried using few examples I found on web (please see bellow) reversing dates stored as string "20231201" into "01122023", but had no luck in contriving in getting the desired date string. They all render wrong results.


Can you please show me the way how to do this using loops only in VBA.

Many thanks in advance.

Sub pasStringToF()
'Not working, result inaccurate in both functions!!!
    Dim sDat As String
    Dim vRslt As Variant
    
    sDat = 19780623
    'vRslt = ReverseDateExmpl1(sDat) 
    vRslt = ReverseDateExmple2(sDat)
    Debug.Print vRslt
End Sub

Public Function ReverseDateExmpl1(ByVal dateStr As String) As String
    Dim i As Integer
    Dim reversedDate As String
    For i = 1 To 8 Step 2
        reversedDate = reversedDate & Mid(dateStr, i + 1, 1) & Mid(dateStr, i, 1)
    Next i
    ReverseDateExmpl1 = reversedDate
End Function

Function ReverseDateExmple2(dateString As String) As String
    Dim reversedDate As String, i As Integer
    ReverseDateExmple2 = ""
    For i = 1 To Len(dateString)
        ReverseDateExmple2 = Mid(dateString, i, 1) & ReverseDateExmple2
    Next i
    ReverseDateExmple2 = Mid(ReverseDateExmple2, 5, 4) & Mid(ReverseDateExmple2, 3, 2) & Mid(ReverseDateExmple2, 1, 2)
End Function

Sub ReverseDate1()
'NOT working
    Dim strDate As String
    Dim strNewDate As String
    Dim i As Integer
    
    strDate = "20231201"
    strNewDate = ""
    
    For i = 1 To Len(strDate) Step 2
        strNewDate = Right(strDate, i Mod 2 + 1) & strNewDate
    Next i
    
    Debug.Print strNewDate
End Sub

Sub ReverseDate2()
'Not Working
    Dim strDate As String
    Dim strNewDate As String
    Dim i As Integer
    
    strDate = "20231201"
    strNewDate = ""
    
    For i = 1 To Len(strDate)
        If i Mod 2 = 0 Then
            strNewDate = strNewDate & Mid(strDate, i - 1, 2)
            If i < Len(strDate) Then
                strNewDate = strNewDate & "/"
            End If
        End If
    Next i
    
    Debug.Print strNewDate
End Sub

Sub ReverseDate3()
'NOT working
    Dim strDate As String
    Dim strNewDate As String
    Dim i As Integer
    
    strDate = "20231201"
    strNewDate = ""
    
    For i = 1 To Len(strDate)
        If i = 5 Or i = 7 Then
            strNewDate = strNewDate & "/"
        End If
        strNewDate = strNewDate & Mid(strDate, Len(strDate) - i + 1, 1)
    Next i
    
    Debug.Print strNewDate
End Sub

 

[Phuoc]

One way:

Function ReverseDateExmple(dateString As String) As String
    ReverseDateExmple = Format(Format(dateString, "0000-00-00"), "ddmmyyyy")
End Function

 

[Denin Srmic]

One way:

Function ReverseDateExmple(dateString As String) As String
    ReverseDateExmple = Format(Format(dateString, "0000-00-00"), "ddmmyyyy")
End Function

Hi Phuoc, is there a way using loops? I am trying to get behind the logic (algorithm) using loops when moving sub-strings within a string rather string functions, Format function or Application.WorksheetFunction.Text.
Thanks for your example!

 

[Joe4]

Hi Phuoc, is there a way using loops? I am trying to get behind the logic (algorithm) using loops when moving sub-strings within a string rather string functions, Format function or Application.WorksheetFunction.Text.
Thanks for your example!

I am not sure I understand the focus on loops here - this is NOT a good use of loops, and trying to use loops actually makes it much harder.
If you don't want to use the FORMAT function, you can use LEFT, RIGHT, and MID function to pull out the MONTH, DAY, and YEAR chunks of the data and rearrange them in a different order by concatenating those chunks back together.

As a side note too: Loops should always be a last resort in VBA. They are extremely inefficient, memory intensive, and can bog down your code performance.
You may not notice it on small data sets, but when running against larger data, it can really become noticeable.
So if there are better alternatives to loops for a particular problem in VBA, it is usually recommend to use those alternatives.

 

[Denin Srmic]

I am not sure I understand the focus on loops here - this is NOT a good use of loops, and trying to use loops actually makes it much harder.
If you don't want to use the FORMAT function, you can use LEFT, RIGHT, and MID function to pull out the MONTH, DAY, and YEAR chunks of the data and rearrange them in a different order by concatenating those chunks back together.

As a side note too: Loops should always be a last resort in VBA. They are extremely inefficient, memory intensive, and can bog down your code performance.
You may not notice it on small data sets, but when running against larger data, it can really become noticeable.
So if there are better alternatives to loops for a particular problem in VBA, it is usually recommend to use those alternatives.

Hi Joe4,
Thank you for your response with a lot of insight as to the loop draw back. I have used all of these functions mentioned above and they all give desired result, hence my reason for asking specific question using loops. It was perplexing for me to see loops used for such a task and was keen to learn how does this works. I was very curious to know why those example (i posted above) do not work. What is the logic behind it. I am still learning VBA programming language, and not all information one can find in books necessary for specific question or task, hence my reason resorting to forums.

 

[dmt32]

It was perplexing for me to see loops used for such a task and was keen to learn how does this works.

as already shown, loops for this requirement not really required but one way you could using a loop - maybe

Public Function ReverseDate1(ByVal dateStr As String) As String
    Dim i            As Long
    Dim reversedDate As String
    For i = 1 To 3
                                          'string         'start Pos          'lenght
        reversedDate = reversedDate & Mid(dateStr, Choose(i, 7, 5, 1), Choose(i, 2, 2, 4))
    Next i
    ReverseDate1 = reversedDate
End Function

Dave

 

[Joe4]

You can easily see how loops would be slower in this example.
To use the FORMAT function or LEFT/RIGHT/MID functions only takes 1 pass of the data, whereas your original attempt takes 8 passes of the data.
Dave's improvement to your loop only takes 3, so that is an improvement, but still not quite as good as not looping at all.

Slowness aside, to me looping does not even really make sense for this problem, as it is not like you are trying to reverse the string completely, or pick out every other character, or some consistent discernable pattern that would lend itself to loops. You are really chunking the data into three sections and rearranging those sections.

Where loops would make more sense would be in the context of let's say that you have a date starting in A10 that repeats every 5 rows down to 200 that you need to do this conversion on. Then you could use a loop to loop through each row you need to apply it to, something like:

Dim r as Long
Dim rng as Range

'Loop through rows 10 to 200, 5 rows at a time
For r = 10 to 200 Step 5
'   Set the range to be fixed
    Set rng=Range("A" & r)
'   Process to rearrange date value below
    ...
Next r

 

[Denin Srmic]

as already shown, loops for this requirement not really required but one way you could using a loop - maybe

Public Function ReverseDate1(ByVal dateStr As String) As String
    Dim i            As Long
    Dim reversedDate As String
    For i = 1 To 3
                                          'string         'start Pos          'lenght
        reversedDate = reversedDate & Mid(dateStr, Choose(i, 7, 5, 1), Choose(i, 2, 2, 4))
    Next i
    ReverseDate1 = reversedDate
End Function

Dave

Thank You Dave,
It works like a charm.

I didn't even know that there is a inherent to VBA CHOOSE function. In Excel I used to combine Excel's CHOOSE function it with VLOOKUP to to make the latter more dynamic in terms of looking up value.
Thanks for this example and showing me how it works.

 

[Denin Srmic]

You can easily see how loops would be slower in this example.
To use the FORMAT function or LEFT/RIGHT/MID functions only takes 1 pass of the data, whereas your original attempt takes 8 passes of the data.
Dave's improvement to your loop only takes 3, so that is an improvement, but still not quite as good as not looping at all.

Slowness aside, to me looping does not even really make sense for this problem, as it is not like you are trying to reverse the string completely, or pick out every other character, or some consistent discernable pattern that would lend itself to loops. You are really chunking the data into three sections and rearranging those sections.

Where loops would make more sense would be in the context of let's say that you have a date starting in A10 that repeats every 5 rows down to 200 that you need to do this conversion on. Then you could use a loop to loop through each row you need to apply it to, something like:

Dim r as Long
Dim rng as Range

'Loop through rows 10 to 200, 5 rows at a time
For r = 10 to 200 Step 5
'   Set the range to be fixed
    Set rng=Range("A" & r)
'   Process to rearrange date value below
    ...
Next r

Thanks Joe4,

I will certainly bear this in mind regarding my next use of loops.

 

[dmt32]

Thanks for this example and showing me how it works.

It was just an idea but if it helps then most welcome & glad we were all able to assist & appreciate your feedback

Dave