Date format in VB created CSV file

[gaz_chops]

I have a small bit of code that copies a sheet from Excel and creates a CSV file and saves it, this works fine, however when I try and import the file into Xero (finance software), the date format in the CSV file has changed from UK to USA format!

In Excel it shows as

25/06/2021​

In newly created CSV file it shows as

6/25/2021

Anyway to retain the original date format?

Thanks
Gaz

 

[JackDanIce]

I came across a date issue like this a few years ago when downloaded Bloomberg data was in UK format, but kept converting into US format on the sheet.
We tried to use VBA to change the month and day around but it still was converting back!

Eventually, one solution was to reset the Windows timezone, restart PC, re-open Excel and it seemed to fix it (or more accurately, we didn't notice the problem again but not the same as problem fixed!) - with this making sure everything was set to English UK (may have done this with language settings too, can't remember now)

If you're using Excel 2013 or later (and I think this should be true for Mac equivalents), you may be better off using a PowerQuery approach which would reduce the amount of VBA code and offer better data controls.

I've recently started speaking to a company that's looking to extract Xero data for several companies to create management reports and part of the reason is to connect Xero directly to Excel, load data into PQ and transform, before output (which can be as a csv file in your case)

 

[gaz_chops]

I came across a date issue like this a few years ago when downloaded Bloomberg data was in UK format, but kept converting into US format on the sheet.
We tried to use VBA to change the month and day around but it still was converting back!

Eventually, one solution was to reset the Windows timezone, restart PC, re-open Excel and it seemed to fix it (or more accurately, we didn't notice the problem again but not the same as problem fixed!) - with this making sure everything was set to English UK (may have done this with language settings too, can't remember now)

If you're using Excel 2013 or later (and I think this should be true for Mac equivalents), you may be better off using a PowerQuery approach which would reduce the amount of VBA code and offer better data controls.

I've recently started speaking to a company that's looking to extract Xero data for several companies to create management reports and part of the reason is to connect Xero directly to Excel, load data into PQ and transform, before output (which can be as a csv file in your case)

We are actually using IRIS Payroll to export data, which I copy into Excel (2010 for Windows in work), then create the Journal and save as a CSV to Import it into Xero.

I haven't used Powerquery, so it would require a learning process, which I currently don't have time for.

I appreciate your suggestions, many thanks.

Gaz