I have a CSV file where all dates are in a U.S. format (dd/mm/yyyy):
However, Excel shows some - but not all - of the cells in mm.dd.yyyy format:
This is on a server in Germany.
Does anyone know what might be causing this, and how I can fix it?
asked Jul 15, 2019 at 22:04
-
3The 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.– AdamVCommented Jul 15, 2019 at 23:14
-
@AdamV That should be an answer IMHO, I think that it's the correct solution.– EJoshuaS - Stand with UkraineCommented Jul 16, 2019 at 13:40
-
Can't help but wonder why the default behaviour of Excel is to interpret data per line and not per column?– BdRCommented Sep 7, 2019 at 9:46
Add a comment
|
1 Answer
Import your CSV file using the the DATA tab. In the "Get External Data" section, select "From Text" file as highlighted in light green:
Navigate and select your CSV file from the pop up windows and press Import:
The "Text Import Wizard" step 1 of 3 should pop up. Configure it to suit your needs and press Next>:
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:
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
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.
