How do I turn OFF the auto correct feature in MS Excel for calendar entries ?
I have a lot of entries like 10-15 in Excel that gets changes automatically to 15-OCT.
Even after I make the fields all text it is still doing this. Can’t seem to find how to stop this. How do I turn this off ?
Thank you in advance
Select all the date cells you’ve entered, Format Cells - > Number -> go down to Date and select the date format you want.
Which version of Excel?
You can’t turn it off.
Type a ’ (apostrophy) before you enter the values into the cell ie **’**5/10 to stop the auto correct.
You can turn it off for a given spreadsheet by formatting the cells as text. You can click on the upper left corner to highlight all cells and format them as text. Interestingly, if you format a range of cells as text and put a simple number in some, the cells are text. You can tell because they will default to left justification and have a small green triangle in the upper left portion of the cell. However, you can use the “text” numbers as numbers in simple math outside the range formatted as text. That is if you format A1 through B1 as text and put numbers 1 and 2 into them, they are text. If you put into C3 =sum(A1:B1) you get 0, but if you put into D4 =A1+B1, you get 3.
You can also format a single cell as text, if you type a space first, so {space}1 is treated as a text 1.
Well, I’m confused. Formatting the cell as text turns it off. It’s always been able to turn it off.
No update?
File >> Options >> Advanced
all the way at the bottom…
check “Transition formula evaluation”
This will disable all guessing of the cell format. But correctly specifying the format beforehand is always the better way.
So instead of 1 - 2 creating Jan-2 I now get -1. Not quite what I wanted…:smack::smack::smack:
And since it’s happening on delimited text to columns - i can’t pre-format the location (at least I can’t find the way). Ugh.
I believe that “autocorrect” is not the right word for what you have in mind. “Autocorrect”, I imagine, would be like if you entered “Apr 31”, and it was corrected to be “May 1” (because April has only 30 days).
The problem here is better phrased as “autoformat”, because a perfectly legitimate date is displayed in a way other than how I want it.
That doesn’t really answer the OP’s question, though. I only mention it because it might help someone pester Microsoft about this.
[rant] It’s been going on forever. I wouldn’t mind so much if I could set the default date format in Options, the way I can choose so many other optional features. I mean, is there any culture in the world that would refer to today as “11-Aug” ??? At least take out the hyphen! [/rant]
Even in zombies, it still can’t be done:
(my bold)
But I do want it formatted as a date! I need to sort it as a date, etc etc. I just want the format to be something that I actually see in places other than Excel. 
In that case, you can choose Date format for that column when doing the Data - Text to Columns. Step 3 of 3 of the Wizard lets you choose Date MDY.
My ancient Excel, ca. 2000, has 16 ways of formatting a date cell. Doesn’t one of those work for you?
Yep.
Most likely spoken as 11th August.
Aug-11 would be taken as meaning August 2011 as dd/mm/yyyy is the standard date format here and according to Wiki List of date formats by country - Wikipedia
quite a few other places with USA and the Federated States of Micronesia being outliers.
Sure, plenty of them do. But Excel requires me to manually format those cells every single time. There’s no option to set my preferred date format. And by the way, setting a preferred date format has been part of the Language & Region options in Control Panel since Win 3.1, and maybe before that. But Excel never got that memo.
Apparently I haven’t been explaining myself well enough. Let’s try this version:
If I go into a brand new Excel worksheet, and I type “8/11”, Excel says to itself, "Hey! that looks like a date! So here’s what I’m gonna do. First, I figure that he means the 11th of August of this year. This year is 2016, so now I’m gonna look up the Julian equivalent of that, and store it in that cell. Then I will display it, complete with the year, as “8/11/2016” in Excel’s Formula Bar. Then, for the user’s convenience, I will display it as “11-Aug” in the cell itself.
I’m okay with all that except the very last step. Why “11-Aug” and not something that I’ve chosen in Control Panel. Or, at the very least, leave it in the format that I typed it in!
People who intend “8/11” to mean the fraction “eight elevenths” have a legitimate beef against Excel here, but that’s not what this thread is about. We’re talking about where I typed a date, and Excel correctly understood it to be the same date that I meant, and then deliberately changes the format for no reason other than to annoy me.
Of course there are many places that put the day before the month.
My question was who uses this specific format, where the day is followed by a hyphen, and then the three lettered abbreviation of the month. In the Wiki link you gave, I saw this only for Romania. And even for Romania, it was listed second, as “also widely used”.
Any Excel user from dd/mm/yyyy format countries who wanted to display dates in a period the year was superfluous would consider using the 11-Aug format because it’s compact and less ambiguous than 11/8 or Aug-11
Try this: Before you enter new data, format the cells in the desired date format. Then when you enter the first cell, Excel should re-format what you entered into the desired display. Isn’t that what you want?
Example: I set an entire column to date format “14 Mar 98”. Then I entered a date into one cell as “03-24-00” and Excel reformatted it to “24 Mar 00”.
That’s what I’ve always done, and never really thought much about it, but Keeve does have a point as to why you can’t set a date formatting default preference so you don’t have to do this every time.