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]
135 comments
-
Matt
commented
This has been an on-going issue for over a decade. Why is it so hard to fix?
-
that zak again
commented
@Joe:
i kind of like the "autodetect dates" thing. it helps when i want to make a list of months, and am too lazy to type it.what i DON'T like is opening a CSV to find that none of my data is intact.
CSV is basically information. it is most often statistical data, and so you would think that such data would be imported as "Text" by default, whether or not it has quotes around it.
or maybe, opening a CSV would prompt you to choose the data type? or maybe a setting in the Excel Options menu for default attitudes? or maybe, giving the user some choice at all???
okay, i know i'm beating a broken record here...
-
Patrick O'Beirne
commented
John, how about getting Excel to follow CSV conventions so that "quoted" strings are retained as strings even if they look like dates such as "SEPT2" ?
-
corey
commented
It seems like way back in the day (Excel 4/5/7), there WAS an interactive dialog much like Access had for doing the same thing. But someone in Excel world decided that was "too complicated", and that aspect got removed. I remember it distinctly... (Excel 95?). So we're left with the default behaviors and assumptions we still bang our heads on our desks against left in, with no obvious way to tweak them.
And IIRC the settings for specific sources could also be saved, like in Access, too.
I think this data import is still being done via ODBC. so adding a SCHEMA.INI file might help for specific data sources/files, but not in the general sense. (google to read up more on ODBC and schema.ini).
I've started using Power Query (Excel 2010, 2013) where possible, to get back the interactive specificity back in my hands as well as sane and expected default conversions, and also to be able to save things for reuse.
ExcelDevs, perhaps redirect the old way to secretly import the data through Power Query now instead of ODBC directly?
Oh, Power Query is easier to use (e.g., its Ribbons) in Excel 2010/13 compared to how it has been integrated into the Data Tools ribbons in Excel 2016+.
-
Joe
commented
I think conversion to date should be only supported with reference to current user locale, without this "clever" date type deductions from Excel.
US:
12/31/2021 - OK
DEC31 - NOT OK
DEC-31 - NOT OK.
31-DEC - NOT OKRU:
31.12.2012 - OK
31дек - NOT OK
31-дек - NOT OK
ДЕК-31 - NOT OK.
and so on.People are very well familiar with what is considered as a date in their locale, stop trying to handle ALL invariants.
If somebody then need to *display* date as "31-DEC" it can be easily archived with cell formatting.
-
not Anonymous
commented
Am in touch with Excel PG. Please add your voice here:
-
that zak again
commented
@john:
either i don't understand what you are saying, or you don't understand how OpenOffice works.
why CAN'T you just use OpenOffice and ditch MS Office? you said you need to update existing spreadsheets that you've made...well, OpenOffice can do that. you can open ANY Excel spreadsheet with no problems.
the only difference is that it will only save as the old XLS files and not XLSX format, but the content is the same. and it DOES open XLSX files no problems.
the only potential issue might be with some proprietary Excel stuff (other than just data in a spreadsheet) that only Excel can do, but 99.9% of users don't even use/need that stuff.
if you need it, then i can see why, but otherwise, am i missing something? -
john
commented
I don't expect this solution will help many but it works for me because I am just a home user of excel for various interests.
The habit of excel changing numbers to dates, however you try to stop it, is so frustrating I had to find a way to allow me to copy something with numbers in it from the internet and paste it into excel without all of the numbers being converted to dates.All of the suggested solutions fail to solve the problem because all of the text conversion ideas then prevent the numbers being used in calculations (unless I have missed something?)
My long winded solution (how crazy can you get) was to install OPEN OFFICE. (FREE!)
I can copy and paste data from the internet into an open office spreadsheet and it keeps the numbers as it should.
I can then copy and paste that data into excel and surprise surprise it keeps the numbers no change to dates !!
(make sure the open office sheet is not left at read only)That works so I hear you say why not just use open Office and ditch MS?
Well over the years I have developed dozens of spreadsheets and at times I need to update them with new data from the internet or other programs I am using.
I do not wish to destroy years of my efforts just because some one in MS does not care about the problems they give their users.
Spreadsheets were originally developed as accountancy or number crunching aids it is a pity that ability now seems to be lost in the latest (help the user) versions of Excel -
D
commented
The sort of solution of formatting cells or other suggestions are about worthless for CSV files.When I open CSV file, it is already "fixed" by Excel and there is no UNDO anymore
In my cases (and seeing # of other comments) this is considered data corruption - there is no beating around the bush as this what that is.
I have no way to know which cells to fix either. I have painful workaround - open the CSV file in Excel and set all fields as text and open it from some other pure text editor - copy from text editor (the whole thing) and in Excel use text import option to overwrite that
Did I mention that it is painful? -
Carmel Abramovitch
commented
I sort of solution and a chat with support ;)
https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8 -
D
commented
I hate to say it (and it pains me a LOT) but they don't care and no amount of complaining and providing proof will change that. The only way in my opinion is to hit their bottom line - sales. Unfortunately even if we and all our friends/family stop buying Microsoft products we would make an invisible to Microsoft difference in sales - just too few of us. if by some miracles Microsoft notices the difference, they still would not know that this is because of this stupid design (and this is not the only one).
They are (like just about any other software company) busy adding support for emojis and connections to Facebook, Tweeter and other social appI still vouched to myself that I will do my best to never buy Office product from Microsoft ever again
-
Anonymous
commented
One wonders how Microsoft would react if we arbitrarily changed the currency used to buy their products while keeping the price the same. 100 pesos/100 dollars/100 marks/100 nickels, it's still 100..
-
john
commented
Others have said it all. It is a total disregard of a users requirements to arbitrarily alter their data without permission. One might hope that there was a government Quango that might intervene or a trading standards unit that could declare the software not fit for purpose
-
that zak again
commented
@Vashek:
click the cell in the top left corner, above "1", and to the left of "A".
right-click any cell.
click Format, and change the format to Text.
now simply save this as book.xltx (Excel Template File *.xltx) in the following directory:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
or wherever your XLSTART directory is.
Excel will never change your text to numbers again.
...at least till the next update. -
Vítek S
commented
Changing data format automaticaly is very annoying, please give as the chance to change this.
-
Vashek
commented
Hello, why, for God's sake, does Excel keep changing the data that I write to a cell? I want to write something and I expect that the same thing will stay there!!! This is of course related to the stupid automatic conversion of anything to a data. But also, another problem is when I have a number and a letter in a cell, I later delete that letter and then suddenly the number (which could be a telephone number, invoice number, order number or whatever) appears with a dot a two zeros behind it. Why does it add decimal places when I did not type them? The thing is that it automatically changes the format of the cell to a number. Why? Why, for god's sake, why???
PS: I use Excel for Mac. This issue is the same for any version of Excel, unfortunately... -
that zak again
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.
