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]
I see the scientific community have become so fed up with Excel Team's failure to actually fix this bug (data corruption is a bug not a design feature) that they've taken the decision to rename human genes to stop Excel from corrupting data...
5 years ago this issue was raised - and your still thinking about it?
Workaround: Automatic substitution of -- by an EN dash.
F. Gress commented
ALL automatic formatting should be default OFF, or a first-time opt-in choice for each user (if online). Automatic formatting is purely annoying and inefficient for anyone who does advanced math, deals with complex content, or just generally knows how to enter exactly what they intend.
automatic formatting is such a stupid function I have met in all those applications, expecially the date format.
+1 to this!
this should really be in every category. Never ever change any data entry without specific permission.
When working with data, opening CSV or TSV files, copying and pasting from other documents, Excel has the nasty habit of changing many entries into a date format. This is done automatically, without verification or any indication that the data entries have been changed.
it should be possible to have a system-wide option to prevent Excel from doing this whatsoever. Many scientists would love to have the power that Excel gives, without the destructive autocorrect-like monstrosities.
Switch it off.
not Anonymous commented
Please comment, retweet and like this tweet/thread to get more visibility:
Jonathan Cooper commented
Interesting. I think you actually have text in A1. You are right, excel is determined to recognize that as a date. An easier way to get what you want might be to go with it. Since Excel wants that to be a date, use it as such. =MONTH(A1)/DAY(A1)
I am importing data from my bnk account as .csv. for some strange reason their formatting for date switches from text in most rows to date format in others. I changed the column to date in Format Cell but it still doesn't line up or get treated as dates.
It would be nice if a date function to convert weirdly formatted dates into something consistent so i could be sorted. I tried Text-to-Columns with / delimiter but it didn't break well.
Or to scientific notatoin. Especially when it is not reversible. Part number are often number, but if excel deletes the end of the number, it has consequences. There must be a way to disable it in excel settings.
Well said, Shan. The strong impression is that MS just does not care. This has been a defect forever, and it beggars belief that any sane-minded person could believe otherwise.
As per 'will lassek', if you combine all the other suggestion items on this very topic, it is one of the most voted. Please CORRECT this issue.
P.S. Why do we have to continually clarify for you the difference between a feature/enhancement/improvement opportunity vs a defect???
The problem is that MS looks at these and says " 'November 27, 1908' votes... WTF??? Don't pay attention to fools like that. They wanna someday not be childlike and really vote so they can be counted, then we'll pay attention."
Welcome to the "November 27, 1908" of us, will lassek.
will lassek commented
excel team (John): please note that there are at least 10+ similar items; if you combine their votes it will be by far the most common request, outnumbering most of the rest
Michael Daughety commented
This is true for cell values as well. Once it is turned to a date...well, all hope is lost. Best example:
Formatted as a fraction A1 = 1/2
B1 = A1*1 = 43832
B1 should = 0.5
Had to write this cumbersome formula to fix it when nothing else would:
User enters 1-2
Microsoft...oh he meant to enter 1-2-2020. I'll just pull a year out of thin air and change thousands and thousands of his data points because I know his data better than he does. Then I'll make it so that he can't fix it back to what was actually entered. Clients just love databases full of altered data.
WHO THOUGHT THIS WAS A GOOD IDEA?!
and and also the (1) -> -1. In fact all the automatic conversion Please do make it drive alone , i'am the driver !
A.C. WILSON commented
It is possible to customize how Excel's Import feature comprehends each data column in a .CSV file.
Nice Brian commented
I regularly open text files or .CSV files with addresses in. Sometimes the first line of the address is something like "1-1" or "2/3". This is a very common way of writing apartment numbers in the UK. Excel "helpfully" converts these values to "1-Jan" and "2-Mar", and it's infuriatingly difficult to convert them back again. IN our CRM system we have hundreds of customers whose address is 1-1, apartment 12 or something, but on our system it now says they live at the 1st of January. It would be great if users could disable this "date sensing" feature, or at least require it to have a year in the number before it attempts to convert to a date. This is an old feature from I think before Excel '97, I don't think it really helps anyone these days.