0

I have a CSV file where all dates are in a U.S. format (dd/mm/yyyy): enter image description here

However, Excel shows some - but not all - of the cells in mm.dd.yyyy format:

enter image description here

This is on a server in Germany.

Does anyone know what might be causing this, and how I can fix it?

3
  • 3
    The pattern seems to be that the dates in dotted format are the ones that are ambiguous - they could be dd/mm or mm/dd. The ones in correct format have a number of days greater than 12 so cannot be interpreted back to front. Import using the wizard and specify the format for those columns and you should be fine. – AdamV Jul 15 '19 at 23:14
  • @AdamV That should be an answer IMHO, I think that it's the correct solution. – EJoshuaS - Reinstate Monica Jul 16 '19 at 13:40
  • Can't help but wonder why the default behaviour of Excel is to interpret data per line and not per column? – BdR Sep 7 '19 at 9:46
2

Import your CSV file using the the DATA tab. In the "Get External Data" section, select "From Text" file as highlighted in light green:

enter image description here

Navigate and select your CSV file from the pop up windows and press Import:

enter image description here

The "Text Import Wizard" step 1 of 3 should pop up. Configure it to suit your needs and press Next>:

enter image description here

On Step 2 of 3, configure your Delimeters for a check in Semicolon, and Other. In the Other box place a quote ". Make sure your check "Treat consecutive delimiters as one". Similar to image below:

enter image description here

IMPORTANT STEP

Step 3 of three you need to select each of your columns with a date in it. Potentially 1 at a time. After you select a column, go up to the upper left area and select date. In the date drop down box select the format MDY. (you state in your question that your source data is in DMY format but if you look at the dates, the second set of numbers is greater than 12 meaning its the days.). When done press Finish

enter image description here

Optional:

If you can get all your dates in as text without formatting applied you can also use TEXT to columns buttons to go through the same wizard.

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.