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 all your support of this request. We are taking a close look at this along with the related requests about maintaining leading zeros when entering values in cells, and to stop changing values to scientific notation.
We know these are important issues, so thanks for all the comments and votes.
Steve [Microsoft Excel]
162 comments
-
ChrizK
commented
Well, it is nice to see someone from MS has actually responded this year, however, the response 4.5 YEARS AGO also said that MS have acknowledged the massive (moronic, idiotic) problem of scientific notation. The "Stop Excel from changing large numbers" thread was also acknowledged as 'working on it' almost 3 YEARS AGO. Wow, MS must have some seriously impressive stuff being developed, if they can't be bothered FIXING BASIC FUNCTIONALITY in mainstay product. A shame Office wiped out Lotus Smartsuite, who were the innovators. I expect they would have recognised customer pain when they saw it.
-
Nathan M
commented
3e-026 != 3e-26
Scientific notation is an absolutely terrible feature in Excel. I don't even use numbers, or calculations and it causes me issues at least weekly with computer names.
3e-026? NOPE! 3.00e-26. Oh, I'm sorry, that isn't what you wanted, Let me convert that to a string for you - 3e-26. I heave searched for an answer to this problem many times over the past 11 years. I am sorry if I seem salty about this issue, it is because I am.
-
not another zak
commented
@not Anon:
i agree with Futski. Discussion is often the best type of Feedback. it shows devs how different people use their software differently than they envisioned, and gives users a chance to help each other and learn together. i fail to see how any of that is not helpful, both to the devs and to the users.@Doug:
i would add option 4.) a setting in Options to let people choose to have the Import Wizard run whenever a non-Excel file is opened in Excel, so that CSVs especially can be imported as text when needed. -
Futski
commented
@not Anonymous - An active, positive and respectful conversation, like this one, is absolutely necessary for MS to see how important the suggestion is to it's user base. It's exactly the type of UserVoice Feedback they're looking for. If no-one was allowed to comment on the suggestion, then MS would have no idea of how important it is.
-
not Anonymous
commented
Guys, this is for feedback/suggestions.
Is you'd like to discuss please move to forums or any other chat platform.
You are messing up the user voice channel.
Thank you. -
Anonymous
commented
I mean the problem is so bad that a paid solution just to handle this has been created: https://flatfile.io/ Completely ridiculous if Microsoft just designed their sofwtare with basic design principles and the end user in mind their product could do most of this i'm sure.
-
Doug
commented
I would really appreciate one of these features when opening a CSV or similar type of file without having to use import wizard:
1.) Excel has a global option that can be set to simply not automatically format data by default that you can turn on (opt in to) so opening a file normally will retain the original data.
2.) Have a Global setting where the default column type is Text instead of General when opening any document.
3.) Have a feature where when you open a file and it auto formats the data in various columns, that there is a way to quickly visually identify these columns and revert to original data if desired by a quick click.
-
Joris
commented
"I believe in the vast majority of cases, when "12/31/2021" is imported from CSV (or entered into a cell), users will want to have this converted to date"
Actually, for the majority of users, this is *not* a date: a year does not have 31 month.
Date and time formats vary wildly around the globe, and in most cases, US usage is a minority usage - however, Excel imposes US usage (with sometimes slight local variations) to everyone.
I'm living in Europe, I'm working with a lot of people and companies from different EU countries, and not a single time in the last 20 years, I've come across a date written 3-04. Yet, depending on what language my Excel is, it will be interpreted as 3d of April or 4th of March.
During data import, Excel gets the automatic conversion of data formats wrong more often than not - yet, i have almost no control over it. In our company, we're now switching at least part of the data analysis we do to Python, even though this means a lot of our people can't do it themselves anymore. It's costly, but it's less costly than the problems we have because of the frankly stupid behaviour of Excel. -
Thisisan Alias
commented
My BIG problem with the auto conversion to date is that it's not even consistent for a column. If I import dates formatted as dd/mm/yyyy then Excel will import 01/10/2020 as 10 Jan 2020 and 15/01/2020 will stay as text because it can't convert it as though it was US format. If you're going to auto-convert, at least look at the whole column and work out the format the dates are in and DON'T ASSUME US format, as the majority of your users don't use that format.
-
D
commented
I was planning to be quiet but I would like add
This conversation is very civilized and educational for me also. I don't remember anybody in this thread being negative, just stating their thoughts, experiences and suggestions.
When I have mentioned "data corruption" I also meant the same thing as anonymous zak explained. -
not another zak
commented
@Futski:
i figured you would reply again after what i said. i don't hold it against you. public discourse in this case especially can be very educational.i think that what you are worried about is unfounded though. you can always CHANGE how your dates display, and it is simple, as the place to change it is on the same screen as the place to change the TYPE of data (right-click and Format Cells). this is ALWAYS an option, as long as your ORIGINAL data is not changed or corrupted irrecoverably.
what i am talking about is like when you have DEC10 (as a product model, for instance) and Excel changes it to the date 12/10/2021. once it has done this, changing it to TEXT format will NOT give me my DEC10 back. a date is stored in Excel as a Unix timecode (a string of numbers), so it no longer has DEC10 in memory, thus corrupting data.
if it were stored in memory as DEC10, it would not be a big concern for me to change it back to TEXT, but as Excel gives no such option, it is literally CORRUPTING the original data.
it does this with OTHER data as well, but for the purposes of the OP (and so i don't get accused of "thread jacking" again), my example was where it changes non-dates to dates.
-
D
commented
I would like to add few things.
10% in my opinion is a large percentage so if this is the case, it should not be ignore. Of course, now is the questions, what percentage is the cutoff point and the answer is "I don't know/ it depends". As this is for some people data corruption, it should be taken care of in some way.
Also, if one things of how large the user base for Office/Excel is, even 1% is # of millions of people.
On top of, what has been called 90% of cases, I believe that it probably is BUT I actually don't remember that i ever wanted for Excel to be smart and help me here nor I have I seen with any other people I worked with so for me that would close to 0% of usage cases and I am pretty sure that I am very uncommon user
And I have been using it since ver 1.x (don't remember exactly which one but it was LONG ago) -
Futski
commented
@Zak,
You got me to lie. :D One more response. But I think we'll need to agree to disagree on some of the finer points of this discussion. In general we all agree that data import/entry needs to be improved. I just think we disagree on what the default method should be.I believe in the vast majority of cases, when "12/31/2021" is imported from CSV (or entered into a cell), users will want to have this converted to date so they can change how it looks and acts in Excel. The user may want to view it as "31-Dec-20" or "December 31, 2020" or Thursday" or even do math on it. None of which can be done if it's imported as text (unless the user converts it back to date).
It boils down to majority rule: If 90% of the people need it imported as a date, then the 10% who need it imported as text should need to make sure the data gets imported the way they need it. Granted, this is also the exact reason for this discussion.
But as I've said before, The CSV import needs to be dramatically improved to allow the user to import in the desired format. We should not be required to rename files to .txt just to import files in the desired format.
'nough said.
-
not another zak
commented
@Futski:
i think i agree with D, but not necessarily with you. i don't believe that Excel should EVER try to change any data, for ANY reason. if i want to use shortcuts, that should be an OPTION that i can disable.
as for the program making suggestions, that is giving me PTSD flashbacks of Clippy *shudder*
besides, if you want to specify column types ahead of time, you can simply import the CSV using the Data Import From Text option.
what i am saying is that Excel should not change data if i simply double-click to open a file without specifying anything. it doesn't change data saved in an XLSX (or XLS) file, so it shouldn't do that for anything else, either. at least, in my opinion. -
Futski
commented
@Anonymous,
I certainly understand peoples desire to use Google Sheets. And I have nothing against it personally. However, many time people are not really given a choice. In my case, I work for a very large multi-national corporation which has standardized on O365. Google simply does not meet either the demands or security requirements of our corp. so there isn't really any other choice for many of us.@D
I think you are saying exactly what I said a few posts ago. Excel could "Suggest" a column format for a CSV file, but the user would/should have the final say as to how to import the data.I'd like to stress that Excel should SUGGEST before actually doing any conversion. That way the user, who actually understands their data better than Excel does, can make sure the correct format is used before any data corruption takes place.
One of the ways to get unconverted data would be to create that new "As Is" format. It would allow the user to specify "As Is" for any CSV column ( or any "As Is" formatted cell ) to retain the original data, exactly as input, no conversions.
And I know many of you want the data to default to no conversion. Unfortunately, if Excel were simply changed to default to no-data-conversion, that would break many hundreds of thousands of macros and other automated processes. And that's why they can't change the default to no-data-conversion. But, at the very least, they do need to allow the user make the decision, which CSV's don't currently do.
In my roughly 30 years of using Excel, I too have imported many thousands of CSV's and have had to repair way too many corrupted cells. I'm simply trying to find a good workable solution that won't break what's already working and yet allow the user the ability to get what they want w/o corruption. Remember, for one person "12-15" might be a date, to another it might be a part number, and to another it might be a formula. It really does come down to the user being responsible for making sure the right input format is being used.
That being said, there does really need to be some way to prevent data conversion. And a new "As Is" format might be the answer. But then again, would "As-Is" really be nothing more than "Text" format.
Sorry, too wordy. I'm done and won't be replying again.
-
Anonymous
commented
@Futsuki If you try Google Sheets it pretty much does that except for the 123E0 which it converts to 1.23E+02 which could argue either way as it's more likely to be correct. Long numbers don't get converted and Google sheets, like Chrome is better than Microsoft's version.
-
D
commented
@Futsuki mostly
Well, the original request seems both generic and specific to me but I see lots of cases which in my opinion are applicable to the request yet my guess at least some were not thought of during that request.
One of my frequent cases is where I open CSV file (which is pure text) and Excel converts data based on what it thinks that the data type was intended but it is frequently wrong and it being pure text, it should never unless specifically OK'ed by the user to convert ANYTHING.
Now, I also use it for "normal" stuff too and it also converts what I don't want it converted and even if it has the data type correct, it is almost always the wrong format - I used different date formats depending on what the data holds so even then I don't want it converted.
Now for suggesting special cases - there are so many possibilities (for dates alone and what about numbers?) that I don't think it is feasible to include them all and we already have generic TEXT format.
I believe that correct choice is to ask if OK to convert with default being NO, AND have an option to never be asked for it and just assume NO (or maybe have 3rd option where assume would be always YES but user is likely to shoot themselves in the foot sooner or later with that).For me at least, it is rare to have Excel to make decision for me as I cannot think of instance where it was correct unless I did not care about the exact format but this is rare in my cases
-
Futski
commented
@Zak,
I think I understand where you're coming from. Perhaps the best solution would be to have a new cell format called "As Is" or something similar. With that format, a cell would get exactly what was typed. i.e.. if 123 was entered, then it would return a number, but if 123E0 or 01-12 was entered, it would be returned exactly as entered, including leading zeros, or essentially as text. In other words, Text and Number would be the only options. Essentially a stripped down General format that only returns Text or Number, but not date or any other kind of conversion. And include leading zeros if entered for a number.We need to remember that most people rely on Excel to convert data to the most appropriate format. It's the exceptions that cause us all this heartache. I know that I frequently enter a date as 12/31 and like that it formats it to a date. So the real trick is to create a NEW format that does NOT do ANY data conversion. And include this new format in the CSV input dialog as an option too.
Did I do better this time? :D
-
not another zak
commented
@notAnon:
nobody is "thread jacking". everyone that i've seen so far has been referring to the topic above, which is (generally) Excel corrupting peoples' data...@Futski:
the problem with that is that not everybody stores all the same type of data in COLUMNS. sometimes it is in ROWS.also, Microsoft is historically bad at guessing. suggestions are not really needed here. if it was typed a certain way, even if its DISPLAY is changed, it should NEVER change the TYPED data...
-
Anonymous
commented
Open excel enter 1-0. Microsoft alters your data to 1/1/2000. Rule#1: NEVER ALTER THE USERS DATA!!!!!!!
Rule#2: NEVER BREAK RULE #1. As I said before, this issue has cost people their jobs because no one would ever think that excel would be altering your data in this manner. Microsoft has been looking into this for 25 years!! Just say that you aren't ever going to fix it so we can be done with it and accept that Microsoft has embraced mediocracy.