Disabling Auto Conversion of Dates in Excel

Copper Contributor

Can auto conversion of dates in Excel be disabled permanently when importing a CSV? This article seems to indicate that it can, but I can't get it to work: https://insider.microsoft365.com/he-il/blog/control-data-conversions-in-excel-for-windows-and-mac


All the dates in my CSV file are in yyyy-mm-dd format. When I click on the CSV, it imports the CSV into Excel but it converts dates that are greater than 1900  (maybe 1904) to date format. It leaves the dates before 1900 alone.  Among other things, this means the column will not sort properly. 


I know that I can import it correctly by opening the file from within Excel and changing the fields to be text first before importing, but this is a pain. Can auto conversion of dates on import really be turned off permanently, without having to import from within Excel?


2 Replies

@Bruce_Wiland Well, the trick is to NOT just click the CSV that lets Excel do some best guesses that don't always give the desired results. Even the insider feature you refer to in the link is not going to help you all  the time.

Always import the CSV data with Get Data, TXT/CSV or via From Text (Legacy). That gives you full control over all data types in the data set you are working with. And not only just dates, but also numbers in different formats than your own locale, or texts that look like numbers but must remain texts.

Thanks Riny. That's what I was afraid of. I was hoping one could totally turn off the auto conversion under all circumstances (and not let Excel make its best guesses), but, unfortunately, I guess that is not possible. It just means I will have to go through a lot more steps each time.