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]
123 comments
-
zak to the drawing board...
commented
don't you know, D? "advanced users" use Macs! lol
seriously though, i have found that MOST of the "simpler users" have never even used Excel. they had no idea what it did, what it was for, or how to use it.
honestly, i'm not sure I know anymore... -
D
commented
There has been a trend for few years to have things more user friendly (in computer related industry, not only Windows). In theory this is good thing but in general I am finding the execution of that very poor (in general with only very few exceptions). Also, there is trend in Windows to start to hide or even remote some of the advance options. Office is following that too. Due to what I mentioned, they are more and more targeting simple users and in some way don't give a **** about advanced ones anymore.
I am really sick and tired of the changes in computer industry (including smart phones which I find to actually be getting dumber in lots of ways) - the trend to try to anticipate my needs and proactively help with no option to disable is really common and getting worse.
We can voice this problem all we want and I doubt anything useful will come out of this. This only way to try to fight it is to stop using their products. I promised myself that I will do my best not to upgrade Office ever again on my home systems - I will look into all other possibilities first.
I work in computer industry and I started to hate it due to all the dumb changes that are happening -
Anonymous
commented
Microsoft can fix the issue, they just choose not to. Basically there are two groups of people. Us professionals who work with data for a living who see 1-1 as just that, 1-1 AND people who do not work with data for a living but find it helpful to have Microsoft think for them. They see 1-1 as.."hey I think this person is trying to enter a date, let me change the data for them because they are too lazy to finish entering two more numbers themselves. Microsoft has chosen to side with non-data users and automatically alter thousands of your clients records without telling you. I have seen people lose their jobs over this incredibly irresponsible decision. Is it really worth people hating your product because you cant simply add an option to turn this off Microsoft? At no point should a software EVER alter ones source data. 1-1 is not 1/1/2020 (or whatever year this post is read at in the future)
1-1 changed to 1-1-2020 = someone is going to lose their job if they aren't paying attention. -
Anonymous
commented
I'm sick of this, it's been an issue for ages and still not fixed. I don't get why the default is to automatically corrupt your data, with no option to turn it off.
-
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
