csv pasting dates randomly

[rfletcher35]

Hi,

I am importing a csv through my code from Autotask PSA to excel so I can manipulate the data. However on date columns no matter what I have tried so far some of the dates appear correct but some don't.
It appears that no matter what I do it goes in as m/d/y instead of d/m/y even though the original data is d/m/y.

This is a portion of my code
wb1.Activate
Range("A1:AJ11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Client - Data.xlsm").Activate
Sheets("Original Data").Range("A1").PasteSpecial Paste:=xlPasteValues
Range("AB:AB,AE:AE,AF:AF,AG:AG").Select
Selection.NumberFormat = "d/m/yyyy h:mm"

This is what I get

1/4/2021 5:09​

1/4/2021 16:21​

1/4/2021 10:29​

1/4/2021 10:00​

1/5/2021 11:40​

1/4/2021 9:27​

1/5/2021 11:26​

1/6/2021 15:10​

25/01/2021 07:19

1/5/2021 8:47​

1/6/2021 10:22​

1/8/2021 8:43​

1/4/2021 10:20​

1/5/2021 10:29​

1/4/2021 15:59​

As you can see the dates are m/d/y and for some reason anything after the 12th seems oddly to maybe show correctly?

Can anyone help with this please?

Thanks

Fletch

 

[rfletcher35]

Update!

If I open the CSV manually the dates remain as d/m/y within the csv

If I open using my code the dates change to m/d/y I have pasted the code below that I am using. Someone please help!

'Open Downloaded Data
Dim wb1 As Workbook
Dim fileNameAndPath As Variant
fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Select your Downloaded File")
If fileNameAndPath = False Then Exit Sub
Workbooks.Open FileName:=fileNameAndPath
Set wb1 = Workbooks.Open(FileName:=fileNameAndPath)

Hopefully

Fletch

 

[rfletcher35]

Hi guys,

I have fixed the issue, Excel VBA when opening CSV files automatically by default changes dates to US format. so you must add "Local:=True" to your code opening the csv

Thanks

Fletch