csv pasting dates randomly

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
261
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
261
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 20, 2011
Messages
261
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Forum statistics

Threads
1,141,619
Messages
5,707,435
Members
421,508
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top