How to retain date format from raw csv file as is when opened via MS-office Excel?
Matthew Martinez 
I have data given to us via email/hard drive which has randomized future dates (ex: 21/02/2200) in multiple formats.
During data transfer 1st mail, the file sent has date format in DD/MM/YYYY.
During data transfer 2nd mail, the file sent has date format in DD/MM/YY.
When I download the 1st data transfer mail file to my Desktop (MS office-365), I see the format is retained.
But when I download the 2nd data transfer mail file to my Desktop, I see the format is not retained. For ex if the source file had dates like 3/6/37 and when I open them in my Desktop the first row becomes 3/6/1937 and rest of the rows remains as is 4/6/37, 5/6/37 and so on.
But when I do the same in my laptop (Excel 2016), all the rows are changed 3/6/1937, 4/6/1937, 5/6/1937
How can I ensure that both my desktop and laptop reads the source file data as it is without any format change. Because there are million of records and I don't wish to have such issues which might totally make our data useless.
I basically want my excel app to open the source file as is without any format changes. Whatever is in source file display them in my file as is.
Can you help us?
1 Answer
For CSV files, you should be importing, not opening the file.
When you do this, you will see an option to set the date format for the relevant column. The date format you set should be the one being used in the file you are importing.
If you do this, Excel will be able to interpret the date properly.
If you really want to see zero change in "how it looks", and are not concerned with how the data is being interpreted, you could specify that column type as being Text. But then it will not be a "real" date that can be used by Excel for any calculations, or sorting in date order.
If you import using Power Query, the query will remember the format the next time you import that file. If you use the legacy wizard, you will need to specify it each time.
Another method to prevent Excel from confusing your dates is to ensure your Windows Regional short date setting matches that in the CSV file. That is more of a bother as it changes things system-wide, but would enable you to open the file.
1
