Date from CSV file not copying & pasting correctly. Day and Month are reversed.

[HighAndWilder]

I am copying data from a CSV file and pasting it into a new worksheet.

One of the columns contains a date structured as DD/MM/YYYY.

When this data is pasted into the other worksheet the day and month are reversed where the day is less than 13.
These dates end up as MM/DD/YYYY.

What is the best way to transfer this data to ensure that the data is copied correctly?

Thanks.

 

[jkpieterse]

Your bigger problem is that ALL DATES IN YOUR FILE ARE WRONG:
Dates with a day less than 13 have been converted to a date with the wrong day in the wrong month
Dates with a "day" of 13 or more have been converted to text.
Do not open a CSV file directly. Open Excel first. Then click Data, Get Data, From File, From CSV. Point at the file and click Transform data. There you can tell Powerquery what the expected date order is when you are converting dates. Use the "Using Locale" option and set it to whatever locale has the same date order as the order in your CSV. See: Excel: Importing text files into Excel sheets