How to convert text dates into date format like mm/dd/yyyy in Excel

Question

I am getting dates in text format in excel cells like -

	date
123	19-Jun-2018
124	09-Sep-2019
125	01-Jan-2017

I want these text dates to look like 06/19/2018, 09/09/2019, 01/01/2017 respectively.

So, how can I do it?

Answer 1

Here's a variation of @PEH's solution using the Win32 API to do the actual conversion:

Private Type SYSTEMTIME
    wYear           As Integer
    wMonth          As Integer
    wDayOfWeek      As Integer
    wDay            As Integer
    wHour           As Integer
    wMinute         As Integer
    wSecond         As Integer
    wMilliseconds   As Integer
End Type

Private Declare Function SystemTimeToVariantTime Lib "OleAut32.dll" _
   (ByRef lpSystemTime As SYSTEMTIME, ByRef vbtime As Date) As Long

Public Function ConvertDDMMMYYYYtoDate(ByVal DateString As String) As Date
    Dim DateParts() As String
    DateParts = Split(DateString, "-")
    

    ' convert text month into numeric month
    Dim Months() As Variant
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") ' these need to be adjusted according the language used for the text dates
    
    Dim NumericMonth As Long
    
    Dim i As Long
    For i = LBound(Months) To UBound(Months)
        If DateParts(1) = Months(i) Then
            NumericMonth = i + 1
            Exit For
        End If
    Next i

    Dim udtST As SYSTEMTIME
    Dim dtmDate As Date

    With udtST
      wYear = DateParts(2)
      wMonth = NumericMonth
      wDay = DateParts(0)
    End With
    
    If CBool(SystemTimeToVariantTime(udtST, dtmDate)) = True Then
      ConvertDDMMMYYYYtoDate = dtmDate
    Else
      ' Return this in case of an error
      ConvertDDMMMYYYYtoDate = CDate(0)
    End If
    
End Function

Answer 2

The only reliable way to interepret texts/strings as date is to split them up and convert them into a real date using the DateSerial function:

Option Explicit

Public Function ConvertDDMMMYYYYtoDate(ByVal DateString As String) As Date
    Dim DateParts() As String
    DateParts = Split(DateString, "-")
    

    ' convert text month into numeric month
    Dim Months() As Variant
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") ' these need to be adjusted according the language used for the text dates
    
    Dim NumericMonth As Long
    
    Dim i As Long
    For i = LBound(Months) To UBound(Months)
        If DateParts(1) = Months(i) Then
            NumericMonth = i + 1
            Exit For
        End If
    Next i
    
    ConvertDDMMMYYYYtoDate = DateSerial(DateParts(2), NumericMonth, DateParts(0))
End Function

Public Sub Example()
    Dim MyDate As Date
    MyDate = ConvertDDMMMYYYYtoDate("19-Jun-2018")
    
    ' you can now write a real numeric date to a cell
    Range("A1").Value = MyDate
    ' and format it the way you want
    Range("A1").NumberFormat = "mm\/dd\/yyyy"
End Sub

Note that this will require to adjust the Months array strings to the language the text dates were writte in. This will return a real numeric date that you can write to a cell and format the way you want using NumberFormat. The advantage is you can now always savely change the format of the date (because it is numeric) and even calculate with it.

Any other solution might look like it works but it might fail because they all rely on the date settings of Windows. They might work in one region and fail in another.