Revisiting - Errors with Importing CSV File Date/Time data

[shellp]

Yongle was kind enough to write the code below which works BUT for some reason some of the data in the csv file is not formatting correctly. I think it is the data versus Yongle's code because even importing the data file using data/import/text produces the same erroneous rows of data occur. I checked while importing and the field is formatted like the rest of the data and looks like 07/06/20 16:58 (mm/dd/yy) but once imported is 2007-06-20 16:58. Looking more closely at the data and it seems it is the single digit days i.e. 06, 07, 08 etc. are the ones causing this issue. Any way to fix this? Thanks.

Sub append_csv_file()
    Dim csvfilename As Variant, destcell As Range, i As Variant, ws As Worksheet, Table As PivotCache
    Dim temp As Worksheet, lr As Long, r As Long, c As Variant
    csvfilename = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If csvfilename = False Then Exit Sub
  
'add new data to temporary sheet
    Set temp = Sheets.Add
    Set destcell = temp.Cells(1, 1)
    With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(3, 3, 3, 2, 2, 2, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 2, 2, _
    2, 2, 2, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

'convert dates in specified columns
    lr = Split(temp.UsedRange.Address, "$")(4)
    For Each c In Array("A", "B", "C", "N")
        For r = 1 To Split(temp.UsedRange.Address, "$")(4)
            On Error Resume Next
            temp.Cells(r, c).Value = CDate(temp.Cells(r, c).Value)
            On Error GoTo 0
        Next r
    Next c
      
'now copy data to sheet "Raw"
    Set ws = Worksheets("Raw")
    Set destcell = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    temp.UsedRange.Copy destcell
    ws.Select
      
'sort descending order
    With ws.Sort
    .SortFields.Clear
    .SetRange ws.Range("A2:X" & ws.Cells(Rows.Count, "W").End(xlUp).Row)
    .SortFields.Add Key:=Range("E2"), Order:=xlAscending
    .SortFields.Add Key:=Range("G2"), Order:=xlAscending
    .SortFields.Add Key:=Range("T2"), Order:=xlAscending
    .SortFields.Add Key:=Range("S2"), Order:=xlAscending
    .SortFields.Add Key:=Range("C2"), Order:=xlAscending
    .Header = xlNo
    .Apply
    End With
  
    ws.Range("AA2:AI2").AutoFill Destination:=ws.Range("AA2:AI" & Cells(Rows.Count, "W").End(xlUp).Row)

'delete temporary sheet
    Application.DisplayAlerts = False
    temp.Delete
    Application.DisplayAlerts = True
End Sub

 

[shellp]

Hello

I seem to have figured out some of the issue which is that I changed the TextFileColumnDataTypes to 4 (for DMY) from 3 (MDY) so that solves it for all but the birthdate column (H) (which isn't currently in the code above but I added it in my testing). But what is happening there is because the report is only providing 2 numbers for the year so some of the birth years are showing as 2029 instead of 1929. Any assistance to fix that via the code? Thanks very much.