text to columns - Date format

[Dummy Excel]

Hi All,
I am trying to change my date format in text to columns to MDY, although cant work it out.
What I have so far is

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

any help would be greatly appreciated
thanks
Sam

 

[Peter_SSs]

What do you have in your worksheet? Details/examples*
What do you want to achieve? Details/examples*

* I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dummy Excel]

Hi Peter,
profile updated
unfortunately work server wont allow me to open excel with the addin loaded due to security reasons. I have tried a couple of times just in case. because of this i cant provide visual examples

what do i want to achieve, change the date format when in delimited (text to columns)

thanks
Sam

 

[Peter_SSs]

Thanks for updating your profile.

I would like to understand what you have in the column to start with and what you want to finish with and where those results will go.

A written explanation would help, and you can also simply copy paste a small section of a worksheet directly into your post if you think that would help us understand.

Example of direct copy/paste

Name	Sales
John	5​
Kelly	10​
Noreen	6​
John	5​

 

[Dummy Excel]

sample data
column A (there is a header row)

5/12/2021 14:48​

5/12/2021 22:00​

5/13/2021 7:00:58 AM

5/13/2021 7:01:00 AM

for some reason the raw data provides two different formats for the date. Not sure why, which is why im trying to correct it as you can't sort the data properly.
there is data in column B.

what im after is dates and times in separate columns.

12/05/2021​	14:48:00​
12/05/2021​	22:00:00​
13/05/2021​	07:00:58​
13/05/2021​	07:01:00​

Date format in dd/mm/yyyy. Would be nice to have time format in 24hr time with AM/PM although not essential

thanks
Sam

 

[Alex Blakenburg]

Your dates are already corrupt.
How are you receiving the data ie csv excel other ?
How are you getting it into Excel ?

 

[Dummy Excel]

Your dates are already corrupt.

when you look at the data on the system it is all in US date format

How are you receiving the data ie csv excel other ?

yes its a .csv file

How are you getting it into Excel ?

from the cloud based system, clicking download. Then opening up the attachment into excel

 

[RoryA]

Either try importing it instead of opening the CSV file directly, or open the CSV file in code which should default to using US regional formats to interpret the text.

 

[Alex Blakenburg]

You can't fix the date you have in Excel already since some of the values have been assumed to be dates and have had their month and day reversed and others could not be converted and are treated as text.
I have on your previous post recommended importing the original file using Power Query but I assume based on the no response, that it was not the direction you wanted to head in.
If you want to give us a sample of the csv file, I am sure one of us can give you some VBA code to import the file and convert the dates.

@RoryA - I tried renaming a file with a text extension and importing it but dates with time on the end are not being converted by the using the MDY import date format.

 

[Alex Blakenburg]

This might get you by:

Sub ImportCSV()

    Dim FileFullName
    Dim colNoDate As Long
    
    '----------- Enter csv file name AND which column no contains the date ------
    FileFullName = "C:\Documents\Software\Excel\Test\TestDates.csv"
    colNoDate = 3

    Workbooks.OpenText Filename:= _
        FileFullName, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(colNoDate, xlMDYFormat)), TrailingMinusNumbers:=True

End Sub