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]
To PP on 6/30, automatic date conversion is fine, and I would even tolerate having it be the default (though not ideal in my work, and really I don't think it is okay to fundamentally change a value based on what excel thinks is a date without giving a way to revert back). Just need an option to choose/avoid it. I don't think that will break anything.
The big problem is that a lot of sheets currently used 'in the wild' rely on automatic date conversion (through VBA or not), so if MS makes changes it will break a huge amount of existing models.
Another vote for the change.
Another vote for this from a professional user. I work with large data sets with gene names. This is a huge problem that excel still changes some gene names to dates based on text alone. Not okay to begin with - but especially with no option to turn it off. Thus importing my data, I cannot trust that it is accurate. Even when I change it back to the proper text (assuming I have found all the errors) with an apostrophe ' my data is already corrupted and I can't seem to recover it such that I can even sort by gene name anymore. Please please please please fix.
Chemical Abstracts Service (CAS) numbers are not dates! For example, the CAS number for silver chromate is 7784-01-2. Excel converts this to 1/2/7784 and switches the cell format to Date. Please, please, stop doing this!
Why is this a thing? All I want is to type in 5-10, 1-20, as in ranging from 5 to 10 and 1 to 20, but there's no option to stop it from making it May tenth and january 20th
Please disable the default 'General' format in Excel, which is too eager to interpret dates. Includes reversing mmddyyyy to ddmmyyyy (where days <=12), when the date format is not the same as your computer region.
Jakob Pilgaard Pedersen commented
This suggestion has been under review for almost 2 years. Give some feedback MS!
This has been a problem for me on many occasions, and sometimes I didn't find out my mistake until much later. Apparently I''m not the only one- google "One in five genetics papers contains errors thanks to Microsoft Excel"
John Arnold commented
this correction is long overdue. From being a daily #$%%. Time is almost never a variable in my work and to have something coverting everything into time.
Chemical FeBr2, iron bromide, anyone care what date that must be? Only excel.
And if you import a large data set, 1-2 hours of cleaning will be required no matter how it is imported....unless of course, you are smart enough to import into a competitve spreadsheet program which I have to do.
any news on this?
not Anonymous commented
open since 2015... and this thread since 2010: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/stop-auto-correction-of-number-into-a-date/9968c54a-221b-4b18-a3d1-cfd3d312a8a6
When can we finally expect a solution here? This is really very annoying.
Instead of having excel automatically set to change numbers separated by - or / to dates let me decide what I want by typing it in. If I wanted dates I would type them in myself. I don't want to have to add extra characters or change the formatting of every cell I enter a range of numbers into
This is a very widespread problem in the scientific community. If you need proof, here is a link to a recent paper: that has been viewed over 56000 times.
"Gene name errors are widespread in the scientific literature"
"...The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions."
If this many scientists have problems with Excel, this is not a trivial problem.
The paper also points out that Google Sheets does not suffer from this problem.
I'm NOT crazy! Thank goodness. Please allow us to turn off auto conversion to date.
** Our value of Pi is 2 rather than the generally accepted value of 3.14......
We will consider changing this if we get enough votes..... really?
I've just run into this opening a .csv file in excel. The .csv file contains a list of oil and gas well data. Well names are formatted using "/" & "-" as delimiters. Where well names can be parsed into date format Excel does this automatically when it opens the .csv file and losses the original value.
eg, Well 1/04-1 is converted into 01/04/2001
And then when i attempt to revert it back to its original value (text value) it returns 36982
This is another 'feature' that should not be prioritized on votes - it should bloody well be fixed!
William Farrell commented
There are many options to disable various auto-formatting in Excel, however a persistent problem has been the auto-conversion of numbers that resemble dates into Excel's own proprietary date serial number. This is more than mere formatting as it changes the actual value of the cell! This is a long-standing problem, dating back in forums for at least a decade (http://geekswithblogs.net/Patware/archive/2005/10/11/56647.aspx) and actually causes serious problems in scientific research (http://geekswithblogs.net/Patware/archive/2005/10/11/56647.aspx). Simply changing the cell format to "text" is insufficient when importing data into Excel (say from .csv) since, as mentioned, the values have been irreversibly altered from their true value to some arbitrary serial number. This seems to me to be the easiest problem in the world to fix: a simple check box in Options to uncheck "auto-convert dates", yet this known issue has not been fixed over numerous releases. Can something please finally be done about it?
Daniel Hitson commented
1. Excel should NOT drop leading zeros - they were originally there for a reason!
2. Excel should NOT automatically convert long number strings to scientific notation. Whoever thought it was a good idea ought to be banned from computers forever!
Joe Park commented
Additionally, please include an option to prevent autoformatting of numerical string values in CSV files to scientific notation. In practice this behavior appears to automatically drop numbers beyond the 6th significant digit upon saving and has wreaked havoc on the work of many unsuspecting users in our office.