Excel reading date incorrectly

[sam123456]

Hi,


I have a csv file with ~500,000 rows. I am facing an issue with Excel interpreting dates incorrectly.

I download this data from an online platform, where the date is formatted as DD/MM/YY.

I download data from the platform, then copy and paste to an existing csv file which is then fed to a tableau dashboard.

As soon as I copy and paste data to the csv file, Excel is interpreting and changing the data to YYYY/MM/DD format.

For example:
01/02/19 (1st Feb 2019) is being changed to 2001/02/19

The date column in the csv file is set to Short Date.

I tried converting to text so I could manually change the dates - as soon as I do that the date changes to a number like 39610. I cannot change one row at a time as there are ~500,000 rows.

Is there another fix?

Thanks

[Alansidman]

Use Power Query to import the data to the Power Query Editor and then bring it into native excel. This should keep the date data formatted correctly.