CSV Import US Date Format

[rfletcher35]

Hi,

I'm sure this has been covered but I cannot find the answer. I have a csv import that has in column A a date and time, I have downloaded this csv for say just Feb, it works well but I cannot change the date format to UK.
This is causing an issue as when the date reaches after the 12th of the month it gets confused. I really need it to read as UK format so I can filter correctly. I've tried a number of things so far including text to columns but cannot seem to get it to work.

Can someone please help me with this?

Please!

 

[Georgiboy]

Is it separating the data into more than one column or is all of the data in one column after run?

Should there be more than one column?

 

[rfletcher35]

Is it separating the data into more than one column or is all of the data in one column after run?

Should there be more than one column?

I have data in columns A-K but the date time issue is in column A,

 

[Georgiboy]

Does the below rectify the date?

Sub test()
    Dim fPath As String
   
    fPath = "C:\Users\jbloggs\Desktop\test\sample.csv" '< path of csv file

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fPath, Destination:=Range("$A$1"))
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[rfletcher35]

Does the below rectify the date?

Sub test()
    Dim fPath As String
  
    fPath = "C:\Users\jbloggs\Desktop\test\sample.csv" '< path of csv file

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fPath, Destination:=Range("$A$1"))
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

Unfortunately no, I still get the same, see screenshot below

 

[Alex Blakenburg]

@rfletcher35 - things changed when I included Time into the test data. This might work for you.

Sub Open_CSV_US()
    Dim strPath As String
    strPath = "C:\Users\jbloggs\Desktop\test\sample.csv"
    Workbooks.OpenText Filename:=strPath, DataType:=xlDelimited, _
      TextQualifier:=xlTextQualifierNone, Comma:=True, Local:=True

'    Convert Dates in Column A
    Dim rngDate As Range, cellDate As Range
    Dim sTemp As String
    Dim pt1 As Variant
    Dim pt2 As Variant

    Set rngDate = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))    ' <-- If you have a header row change change this from row 1 to 2
    rngDate.Columns.AutoFit
    For Each cellDate In rngDate
        sTemp = cellDate.Text
        Debug.Print sTemp
        pt1 = Split(sTemp, " ")
        pt2 = Split(pt1(0), "/")
        sTemp = DateSerial(pt2(2), pt2(0), pt2(1)) + TimeValue(pt1(1))
        cellDate.Value = CDate(sTemp)
    Next cellDate

End Sub