Excel Power Query date format messup

Question

I'm trying to import dates from a csv file to Excel. The input is in the format of m/d/yyyy, and the same is my system setting:

However, when I change the Data Type setting upon importing, it messes the dates up. Apparently it switches to d/m/yyyy and then doesn't understand the dates:

I have no idea how to change this. Even if I manually change the cell format to date with m/d/yyyy, it doesn't seem to have an effect. How can I make sure my dates get recognised properly with the column type change option without rewriting my input?

Thank you!

Answer 1

I'm not sure about this, but I think Power Query is recognizing the date per your locale, and not per the short date settings. I suggest one of these two solutions:

1. In Power Query, when you change the Date Type setting, do this by right clicking on the column header. Then select Change Type --> Using locale. You should then have the opportunity to select English(US). or
2. Use the Legacy Wizard which should give you the opportunity to specify the source date format at the time of import.

Answer 2

Use Locale Date as explained here:

What we do is select the column with our dates in it then:

• Right click the column
• Choose Change Type –> Using Locale

(Yeah, I know… this is hardly a term that Excel users are familiar with, but it allows you to force a different regional setting on the data source.)

You’ll then be prompted with a new dialog where you’ll choose the date, then the Locale you want to use to read it:

The key here is to recognize WHICH locale your data format is emulating. There are hundreds of countries in this listing. My guess is that you’re probably going to pick either your own or English (United States) most of the time. In truth, when working with dates, the country is actually not the important part. The important part is that you pick a country where the MDY or DMY format is consistent with your data source.