Convert text to date format to yyyy/mm/dd

Question

When a report is exported from our system, dates are shown as 'yyyy / month name'.

I tried changing them to date format by creating a custom one on Excel > Home tab. Only when I click on one column and convert it to text, will it change to date format, from general "2015 March" to date 2015/03/01.

Custom Format not working

I am now trying to run a macro to convert all columns that contains these month/year dates to text, as I found no way else to fix the 1004 message.

Sub Text_to_columns()
'
' Text_to_columns Macro
'

'
Range("H2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Replace What:=" / ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        

Dim col As Range
Dim i As Integer
For i = 8 To 300
    
    Selection.TextToColumns Destination:=Cells(2, i), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 5), TrailingMinusNumbers:=True

Next i

End Sub

It shows

1004 error
Microsoft can only convert one column at a time

How can I fix this? Is there another method to convert those text to dates?

What is the result (in the message box) of the following procedure: Sub showNumberFormat(): MsgBox Range("H2").NumberFormat: End Sub. Just copy/paste and run. — VBasic2008

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-11-07T11:51:28

Your question is not clear, but I think you want to change the text values in those cells to a date format, formatted in some defined fashion.

I suggest something like (and you'll have to change some of the constants to suit your specifics):

Option Explicit
Sub convToDate()
    Const firstCol As Long = 8
    Dim lastCol As Long
    Const myRow As Long = 2
    Dim WS As Worksheet
    Dim I As Long, x As Variant
    
'Set worksheet variable
Set WS = ThisWorkbook.Worksheets("sheet1")

'Find the last column
With WS
    lastCol = .Cells(myRow, .Columns.Count).End(xlToLeft).Column

'convert contents to date
    For I = firstCol To lastCol
        x = .Cells(myRow, I)
        If IsDate(x) Then .Cells(myRow, I) = CDate(x)
    Next I
    '.Range(.Cells(myrow,firstcol),.Cells(myrow,lastcol)).NumberFormat = "whatever format you want"
    'If you really want to convert these to text, use the VBA Format function

End With

End Sub

Convert text to date format to yyyy/mm/dd

1 Answers