Convert text to specific date-CodePudding

I have the following text in cell A1 09-03-22 that's mm-dd-yyyy

the type is general but I want to Convert it into Date with the format dd.mm.yyyy Can be in Excel or with vba.... Because if I change the type to date it always returns as 09.03.2022 or 09 March 2022 ... Excel thinks my month is my day and the other way around. But what I want is 03.09.2022

CodePudding user response：

With VBA, this is one way to do it in the ActiveCell:

Sub TxtDateToDate()

    Dim strDate As String

    With ActiveCell
        strDate = .Value
        strDate = Split(strDate, ".")(1) & "/" & _
            Split(strDate, ".")(0) & "/" & _
            Split(strDate, ".")(2)
        .Formula = .Value   0 'shake cell format
        .Formula = DateValue(strDate)
        .NumberFormat = "MM.DD.YYYY"
    End With
  
End Sub

CodePudding user response：

If I correctly understood your case, it looks that there is a column having inconsistent Date/String data. The existing Date swaps the day with month and the string date starts with month followed by day.

If this is the case, please try the next code. It assumes that the column to be processed is A:A, and returns in column B:B. You can easily change the two columns:

Sub ConvertText_DateToDate()
  Dim sh As Worksheet, lastR As Long, arr, arrD, arrStrD, i As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
  arr = sh.Range("A2:A" & lastR).Value2
  ReDim arrD(1 To UBound(arr), 1 To 1)
  
  For i = 1 To UBound(arr)
        If IsNumeric(arr(i, 1)) Then
            arrD(i, 1) = DateSerial(Year(arr(i, 1)), Day(arr(i, 1)), Month(arr(i, 1)))
        Else
            arrStrD = Split(arr(i, 1), "/")
            arrD(i, 1) = DateSerial(CLng(arrStrD(2)), CLng(arrStrD(0)), CLng(arrStrD(1)))
        End If
  Next i
  'format and drop the processed array content, at once:
  With sh.Range("B2").Resize(UBound(arrD), 1)
        .NumberFormat = "dd-mm-yyyy"
        .Value2 = arrD
  End With
End Sub

But, if the Date part did not swap day with month, you have to use

   arrD(i, 1) = arr(i, 1)

Instead of:

    arrD(i, 1) = DateSerial(Year(arr(i, 1)), Day(arr(i, 1)), Month(arr(i, 1)))