5

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:

System settings

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:

Error

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!

Improve this question
3
  • Did you try to import as m/d/yyyy ? if not import data and finally change it to d/m/yyyy in Excel. – Rajesh S Mar 15 '18 at 11:25
  • @RajeshS Well that's what I'm trying to to, but obviously it doesn't work... Can you show me how to do it? – lte__ Mar 16 '18 at 7:45
  • This you have written, "when I change the Data Type setting upon importing, it messes the dates up. Apparently it switches to d/m/yyyy ". Don't change the data type or the Date format. And you need to change it !!. Simply import in Excel, save the file then select the Date column and change the Date format. – Rajesh S Mar 16 '18 at 10:34
4

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.
Improve this answer
3
  • This mess with the date formats has been going on for AT LEAST 10 years now... Crazy they still haven't fixed it. Change Type --> Using locale works, but I still don't get why can't Excel use the system settings... – lte__ Mar 19 '18 at 10:27
  • OPENing the file will parse per the short date settings, as does just entering a date into a worksheet cell. It seems they changed from relying on the short date settings to relying on the locale when they developed PQ. – Ron Rosenfeld Mar 19 '18 at 10:47
  • 1
    ...thus making international work a pain in the ass... – lte__ Mar 19 '18 at 11:43
0

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

enter image description here

(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:

enter image description here

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.