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.
Jessica
shared this idea
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!
Cheers,
John [MS XL]
119 comments
-
zak to the drawing board...
commented
it took the addition of code to try to "outsmart" users.
but apparently, trying to remember where they put that code is not easy.i will say this only once: no program that i trust will try to change the data i give it.
Microsoft lost what little trust i had for them. i am now happily using OpenOffice, or Google Sheets when i need to share it, as the other user does not need to even have any program installed to view it!
-
Roy
commented
@Anonymous: In the meantime while waiting for retirement, I mean for them to fix this, you CAN import CSV's with the old Import Wizard which did allow you to specify a column's type BEFORE import. Not nearly the same as it coming up just by clicking the file to open it, but "in the meantime"... I repeat a Comment I made for a different Suggestion:
I've lucked across the Dead Sea Scrolls of opening a CSV in "the old manner" in the second answer in a Stack Exchange (Super User) answer:
—— Slightly modified, just the last step ——
(This is different from the version of the old wizard that is presented in the Text to Columns feature in that it still works to do the actual importation, not just to break up already imported or existing data. Same tool, just allowed to act at the moment of importation instead of only after the damage is done.)
Basically, go to Options and make sure that in the selections under the Data heading (at the bottom of them in the "Show legacy data import wizards" section and make sure the third one (at least, required for this... but I've always checked them all, eh?) is selected.
Then, to use the old import wizard the way we all remember it working, go to Data in the Ribbon, choose the first choice on it: "Get Data", and choose "Legacy Wizards" from the dropdown menu.
Here's a nasty foolishness on their part: Pick "From Text (Legacy)" from the dropdown new menu. The foolishness comes from how many people won't know that isn't just .TXT files, but several types of such including .CSV files (and, well, literally anything you think you'd like to try this on for that matter). Anyway, pick it and use the file browser that pops up to find your file and select it.
NOW you finally have the old wizard, functioning precisely as we remember it, and BEFORE it opens the CSV file so BEFORE it destroys information forever. Do like you always did, select "Delimited" and move on, make sure "Comma" is a selected delimiter (really, make it the only one to avoid hassles), and then the magical step... click "Next" to get to the step in which you tell Excel how to import the various columns: as General (it will hammer you if it can find a way), Text, Date (for various layouts), and Skip. (If you are using a different delimiter, you can do a couple other things, but they are not germane to CSV importation.)
Finally, click "Finish" and you're... well, almost home free.
Here's where you'll probably diverge from the Super User answer which assumes you want a continuing connection and a Table. If you do, just choose options as you wish, or don't change anything, just click "OK" and you have a nicely imported Table from the CSV which keeps leading 0's and so on (if you marked that/those column/s to import as Text).
If you JUST WANT A FREAKING BLOCK OF IMPORTED DATA, do one last thing: Uncheck the "Add this data to the Data Model" checkbox (just above the lower left corner's "Properties" button). Now your import will truly be like the old days, just a slick import of the CSV's data with you having had the ability to force the importation approach for any column you wished to do so for. Had date-like data but they weren't dates? You marked them Text and now you're a WINNER! Ruined data is your fault only, like it should be!
-
Anonymous
commented
I think we have to face the fact that Microsoft is never going to make what should be a very simple fix (just enable open csv preferences of "do these conversions" with date, scientific notation, etc). If they haven't done it by now, they are telling us that we just shouldn't use excel for anything we care about. I've already stopped using it for scientific work -- too dangerous to have things changed without me knowing. But when I import a list of student data and it takes someone's ID which is MAR09 and it says "oh, that isn't initials, it's a date!" I just don't know what to do.
-
Ivan
commented
I wish i will see it happens before i die.
-
Anonymous
commented
So we are now changing the scientific names of genes because Microsoft can’t solve a more than 5 year old problem with excel.
-
Roy
commented
@Alex: don't say things like that!
Now those team members, when they see this for the first time on the Christmas Party Whiner Roll will look at each other and as one pour some powdered testosterone on the backs of their hands and snort it with a flourish that would have done them proud in the '80's and whip their heads up as one and roar "RAWRRRRRRR Science b*tches kiss my *ss!!! Kowtow to your masters! All will bow to the mighty ones!"
It just isn't good to be their entertainment and THEN provide extra entertainment!
-
Alex
commented
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?
-
Rodolfo
commented
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.
-
Anonymous
commented
automatic formatting is such a stupid function I have met in all those applications, expecially the date format.
-
Anonymous
commented
+1 to this!
-
Anonymous
commented
this should really be in every category. Never ever change any data entry without specific permission.
-
Anonymous
commented
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:
https://twitter.com/PatrickSeidl/status/1273617606726082566?s=19
-
Jonathan Cooper
commented
@Michael Daughety
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)
-
Phil
commented
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.
Win10 Excel2019 -
Anonymous
commented
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.
-
Anonymous
commented
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.
Ian
-
shan
commented
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???
Jesus Christ.
-
Roy
commented
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.
