Allow us to turn off automatic conversion to date
Search for "make excel stop converting numbers to dates" and you will see the user demand for this feature. Please for the love of all that is holy let this be at least an advanced user setting.
Thanks for the suggestion Jessica. We’ve got a few other suggestions on the site that are similar with scientific notation, etc. We’ll take a look at this area, and pay special attention to any cases that get a lot of votes. So please keep the votes coming to help us do a good job at prioritizing asks like this!
John [MS XL]
David Hawkins commented
Has to be the most irritating function in excel - If I start swearing at the computer its almost always when excel has mangled my data by trying to be helpful and converting things like 7611-01-01 in to a data when its a document reference number. Once its "helped" there is no way back
hmm. while not convenient, I got around this (for my needs) by using Powershel with Epplus.dll. Epplus works on excel file format directly, leaves formatting control to the developer instead of the "helpful" defaults Excel applies (that can't be overridden easily if at all). Avoids kludges like template spreadsheets (not talking about .xslt files), and is amenable to running on a server to boot.
Елена Пак commented
Years and decades will pass by, but still MS Excel will auto convert values into dates. What a shame.
Opening scientific data in excel for review or analysis purposes is a nightmare in Excel. Some values, gene names and other text automatically transforms into dates and then the data is corrupted. Changing cell format in this case corrupts the data even further and there's no way to return to the original value/text, since the data corrupts when opening the file. This is the worst functionality in Excel and I don't know anyone who benefits from the automatic transformation of dates. Please the date transformation optional setting for those who might need it!
There is nothing to hesitate about. Unauthorized automatic data changes should be desabled as default. There is a big line between modifying underlying real variable values and the format in which it is shown. This feature is a global systemic risk and causes economical damage around the world !!!
The date function not only automatically turns things that don't look much like dates into dates, but also in doing so eliminates the original data. For example, a large data set I had included many entries that were page ranges, several of which got mutated into dates. This cannot be turned off. Having the option to label a set of cells as dates is useful, but presuming that I want something to be a date without better reason than a vague resemblance to a partial date is harmful.
حيدر وكيل زاده commented
Why is this so long (from November 07, 2015) and has not been fixed?
microsoft sucks commented
FIX THIS YOU DUMBASSES
This would be a huge improvement. Excel is used often to analyze phone system call detail records. Phone numbers and dialed digits often are very long which are converted to scientific notation or begin with a leading + or leading 0 (which are dropped).
A program must never modify data content without the consent of the user and without warning the user that the program has changed the data!!! All these data converting and formating tools excel has, can be usefull in some situations, but they should be made optional and gathered into a tool box for datamanipulation tools (eg. text to numbers etc...).
You can only imagine how many analysis, study etc.. is wrong because of these autmatic background data alteration functions!!!
And yes, I know all the tricks putting a "'" in front, formating the cells to text type, text to column datatype selection what so ever... . There are too many occasions given to the user to forget or omit this which Leeds to errors. I lose yearly hours of working time when battling with Excel to not to modify my data without my permission.
The data modifaction features cause economical damage, erroneous reports and loss of time to companies.
not Anonymous commented
Finally @msexcel responded on Twitter... If you have an account please respond here so that they see that we really want a solution:
If you don't have an account: create one :-)
automatic conversion has been a pain since the dawn of excel. this can even happen during opening csv files.
please excel, stop that!
Kenneth Barber commented
Can you give an example of when numbers change to dates?
Also, if the cell that you are typing your number into already has date formatting applied, then any number will appear as a date. In Excel, all dates are just numbers formatted to look like dates.
Kenneth Barber commented
Use an apostrophe before you enter your date-like text. e.g. You would enter 3/88 as '3/88.
Change the Cell, Row, Column or whole workbook to use "Text" instead of "General" formatting.
I know it can be painful sometimes, but it's not that bad to fix up.
Worst case I had was when using Excel online version it wouldn't let me change my date formats from US to AUS (days before months, as it should be ;) ) no matter what I tried and how many times I changed it..
This applies to number like text values too... .
A program must never modify data content without the consent of the user and without warning the user that the program has corrupted the data!!! All these data converting and formating tools excel has can be usefull in some situations, but they should be made optional and gathered into a tool box for datamanipulation tools (eg. text to numbers etc...).
I'm sure this is here somewhere but I can't find it. When I input something like 3/88, Excel automatically converts this to a date, in this case Mar-88. Please add a feature where I can turn off automatic conversions like this.
The way that Excel continues to convert CAS numbers, as one example, to dates with no route back is dangerous. We have abandoned Microsoft and now use web forms to enter data to a database. It took more effort to get started but has proved to be less effort than having useless data every time a Microsoft application decided to "improve it" for us. We still use Microsoft but they are no longer essential and are becoming less relevant each year.