Formatting date problem

[blueflash]

I am trying to extract a date from a text string and write it to another column is a spreadsheet.

The date is in the input string in col A and I want to extract this date and write it to col F in the format dd/mm/yy.

In the code below, at the line Cells(c, "f").Value = str1, str1 shows the date in the format I want it - for the date in line 1, str1 is 29/3/22
and for line 2, str1 shows as 2/12/22 but 12/02/22 is written to col F.

Can someone tell me why the incorrect dates are written to col F for lines 2, 3 and 5 and how I can get the dates I want?

Thanks.

Sub driver()

srow = 1
lrow = Sheets("input").Cells(Sheets("input").Rows.Count, "A").End(xlUp).Row
c = 1
For i = srow To lrow
       inputString = Cells(i, "a").Value
        arrsplitstring = Split(inputString)
        k = UBound(arrsplitstring)
        p1 = 0
        temp = arrsplitstring(j)
        ii = Len(temp)
        For jj = ii To 1 Step -1
                If Mid(temp, jj, 1) = "." Then
                        p1 = jj
                        Exit For
                End If
        Next jj
        str1 = Mid(temp, 1, p1 - 1)
        Cells(c, "f").Value = str1
        c = c + 1
Next i
End Sub

 

[MARK858]

Can someone tell me why the incorrect dates are written to col F for lines 2, 3 and 5 and how I can get the dates I want?

Because all dates in VBA are in US format mm/dd/yyyy (the ones that show "correctly" are text as they can't be converted to US dates.

The trouble you now have in column F is they are now mixed format dates so you can't easily do a Text to Columns conversion.

Try the code below on a copy of your sheet, I am assuming your headers are in F1 and your data starts in F2

Sub ConvertColF()
    Dim myCell As Range
    For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        If IsNumeric(myCell.Value2) = True Then
           myCell.NumberFormat = "@"
           myCell = Format(myCell, "mm/dd/yyyy")
        End If
    Next

    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

 

[Alex Blakenburg]

Another option you could try, what happens if you add CDate to this line as per the below:

Cells(c, "f").Value = CDate(str1)

 

[blueflash]

Because all dates in VBA are in US format mm/dd/yyyy (the ones that show "correctly" are text as they can't be converted to US dates.

The trouble you now have in column F is they are now mixed format dates so you can't easily do a Text to Columns conversion.

Try the code below on a copy of your sheet, I am assuming your headers are in F1 and your data starts in F2

Sub ConvertColF()
    Dim myCell As Range
    For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        If IsNumeric(myCell.Value2) = True Then
           myCell.NumberFormat = "@"
           myCell = Format(myCell, "mm/dd/yyyy")
        End If
    Next

    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Thanks for your explanation and solution Mark, I now have the issue sorted.

 

[blueflash]

Another option you could try, what happens if you add CDate to this line as per the below:

Cells(c, "f").Value = CDate(str1)

Thanks Alex. I had already tried that but it did not solve the problem.

 

[Alex Blakenburg]

Thanks for the feedback. Glad @MARK858 was able to get you sorted.

 

[MARK858]

@blueflash You're welcome, happy we could help