How to display sub string in reverse order but rea

Question

I am processing a .txt file in VBA.

Amongst other tasks, I need to read in a string representing a date and display the actual date in Excel.

A date string in the .txt file looks like "190223" This represents 23/02/2019

My challenge is to get this done.

What I have done so far is:

' ... loop

With ActiveWorkbook.Worksheets(1)

' Other statements here

' Event date time
    .Range("N" & i).Value = StrReverse(Mid(.Range(keyword.Offset(0, 4).Address), 1, 2) & _
    "/" & Mid(.Range(keyword.Offset(0, 4).Address), 3, 2) & _ 
    "/" & Mid(.Range(keyword.Offset(0, 4).Address), 5, 2))

End With

But I get the undesired output:

32/20/91 ' For a date string 190223 the desired output should be 23/02/19

Any help would be much appreciated.

Thanks in advance.

Answer 1

Convert it into a real date

You must extract year, month and day of that string and then convert this into a real date.

Then you can format the date to what ever date format you like. The value that is saved in the cell is then a real date value (not a string!) so you can calculate with it.

I highly recommend to read How Dates Work in Excel – The Calendar System Explained + Video to understand the background and why real dates are so important.

Here is an example:

Option Explicit

Public Sub ConvertDateExample()
    Const InputStr As String = "190223"

    Dim InputYear As Integer
    Dim InputMonth As Integer
    Dim InputDay As Integer

    'extract year, month and day
    InputYear = Left(InputStr, 2)
    InputMonth = Mid(InputStr, 3, 2)
    InputDay = Right(InputStr, 2)

    'put it together to a real date
    Dim RealDate As Date
    RealDate = DateSerial(InputYear, InputMonth, InputDay)

    'write the date into a cell
    Range("A1").Value = RealDate
    'format that cell to your desired format
    Range("A1").NumberFormat = "dd/mm/yyyy"
End Sub