Dates getting converted from European dd/mm/yyyy to US mm/dd/yyyy Format

Not sure if this is a PQ or a vba problem … possible somewhere in the middle. But it is causing much grief.

Quick synopsis of problem … It is to do with European (and Canadian) dates getting converted to US format.


Step 1:



N.B. Interestingly, if I do a Save As from here and save a .txt (Tab Delimited) file then everything works ok. i.e the date retains it European format


Step 2:

I want to save the data as a .txt (Tab Delimited) file in a specific directory.

As the system will be used by a non-technical user, I have put the creation of the .txt (Tab Delimited) into a vba macro. (My vba skill level is 1- on a scale from 1 to 10!!)

So I have cobbled this together as best as I could.

The worksheet is copied to another book before saving … to avoid confusion for the operator.

This is an extract from the vba macro:
Step 3:

Import the .txt (Tab Delimited) file using Power Query for subsequent loading to PowerPivot

… and the Date has been reversed … causing my Load to fail.
Any ideas as to where the problem may lie?

Hey Ted,

So I can confirm (based on the text file you emailed to me) that the file is being saved with dates in a US format. Now based on that fact that we've talked before, I'm assuming your default system settings indicate you're in Ireland, and you're not using US regional settings.

If that's true, the only thing I can think of is that the default save as text settings from VBA write in US date formats. (Wouldn't be the first time we've bumped into a US Centric setting in VBA.)

I guess the question I'd have to ask here is... can you just tell Power Query that these dates come from a US system using the "Change Type using Locale"? I know it seems stupid, but the only other alternative I can see here is that we'll need to code up a different method to write the data to the text file.

Hi Ken,

I have been checking the Net for vba posts and you are correct in your assessment of the problem.

Given that users have been complaining about this behaviour since, it looks like 2002, it is unlikely that Microsoft are going to provide a fix any time soon.

As I have already created 18 history files in Batch Mode with European Dates ... it is not possible to suddenly start saving the date in US Format as this would give us a mixture of format which would not work in PQ Get From Folder.


However as I am loading the data into the Workbook before getting the Macro to save it ...I can control the content.


By doing a Transform on the Date field to a "Text" .... it stores the date as 31/05/2016 (left justified).
The vba does not make any changes then and this is what gets written to the CY_2016_05 Cost Prices.txt file


Item Code Date Std Cost
1008 31/05/2016 272.231
1018 31/05/2016 204.828
1023 31/05/2016 191.3


So there is a workaround and this query may be flagged as successfully closed.

Many thanks for your help.

Ted.

Ah, good one! Yes, it's kind of a frustrating workaround, but at least it's an easy fix. (I was thinking a much harder route of writing line by line to the text file.)