# How to turn OFF Excel auto correct for dates? **URL:** \ **Category:** Factual Questions\ **Created:** [January 20, 2015, 6:34pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399 "2015-01-20T18:34:02Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![davida03801](https://avatars.discourse-cdn.com/v4/letter/d/b5e925/32.png) [@davida03801](https://boards.straightdope.com/u/davida03801)\ **Post date:** [January 20, 2015, 6:34pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/1 "2015-01-20T18:34:02Z")
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 ---
**Author:** ![conway](https://avatars.discourse-cdn.com/v4/letter/c/ec9cab/32.png) [@conway](https://boards.straightdope.com/u/conway)\ **Post date:** [January 20, 2015, 6:39pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/2 "2015-01-20T18:39:23Z")
Select all the date cells you’ve entered, Format Cells - \> Number -\> go down to Date and select the date format you want. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [January 20, 2015, 6:40pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/3 "2015-01-20T18:40:16Z")
Which version of Excel? ---
**Author:** ![Lungfish](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@Lungfish](https://boards.straightdope.com/u/Lungfish)\ **Post date:** [January 20, 2015, 6:51pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/4 "2015-01-20T18:51:37Z")
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. ---
**Author:** ![OldGuy](https://avatars.discourse-cdn.com/v4/letter/o/3bc359/32.png) [@OldGuy](https://boards.straightdope.com/u/OldGuy)\ **Post date:** [January 20, 2015, 7:06pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/5 "2015-01-20T19:06:29Z")
> [@Lungfish](#): > > 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. ---
**Author:** ![Leaffan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/leaffan/32/299_2.png) [@Leaffan](https://boards.straightdope.com/u/Leaffan)\ **Post date:** [January 20, 2015, 7:18pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/6 "2015-01-20T19:18:42Z")
Well, I’m confused. Formatting the cell as text turns it off. It’s always been able to turn it off. ---
**Author:** ![Leaffan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/leaffan/32/299_2.png) [@Leaffan](https://boards.straightdope.com/u/Leaffan)\ **Post date:** [January 21, 2015, 3:21pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/7 "2015-01-21T15:21:54Z")
No update? ---
**Author:** ![AdamF](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/adamf/32/6246_2.png) [@AdamF](https://boards.straightdope.com/u/AdamF)\ **Post date:** [January 21, 2015, 4:31pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/8 "2015-01-21T16:31:14Z")
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. ---
**Author:** ![SysGW](https://avatars.discourse-cdn.com/v4/letter/s/e56c9b/32.png) [@SysGW](https://boards.straightdope.com/u/SysGW)\ **Post date:** [August 11, 2016, 8:15pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/9 "2016-08-11T20:15:41Z")
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. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [August 11, 2016, 11:04pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/10 "2016-08-11T23:04:18Z")
> [@davida03801](#): > > How do I turn OFF the auto correct feature in MS Excel for calendar entries ? 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] ---
**Author:** ![Bone](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bone/32/407_2.png) [@Bone](https://boards.straightdope.com/u/Bone)\ **Post date:** [August 11, 2016, 11:26pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/11 "2016-08-11T23:26:03Z")
Even in zombies, it [still can’t be done](https://support.office.com/en-us/article/Stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8): > [@](#): > > Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don’t want changed to a date. **Unfortunately there is no way to turn this off.** But there are ways to get around it. > > Preformat the cells you want to enter numbers into as Text. This way Excel will not try to change what you enter into dates. > > Select the cells that you’ll enter numbers into. > > Press Ctrl + 1 (the 1 in the row of numbers above the QWERTY keys) to open Format Cells. > > Select Text, and then click OK. (my bold) ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [August 12, 2016, 2:24am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/12 "2016-08-12T02:24:10Z")
> [@](#): > > This is very frustrating when you enter something that you don’t want changed to a date. 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. 😕😕😕 ---
**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\ **Post date:** [August 12, 2016, 2:42am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/13 "2016-08-12T02:42:09Z")
> [@SysGW](#): > > 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. 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. ---
**Author:** ![Musicat](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/musicat/32/20189_2.png) [@Musicat](https://boards.straightdope.com/u/Musicat)\ **Post date:** [August 12, 2016, 2:56am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/14 "2016-08-12T02:56:56Z")
> [@Keeve](#): > > 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. 😕😕😕 My ancient Excel, ca. 2000, has 16 ways of formatting a date cell. Doesn’t one of those work for you? ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [August 12, 2016, 3:10am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/15 "2016-08-12T03:10:21Z")
> [@Keeve](#): > > . I mean, is there any culture in the world that would refer to today as “11-Aug” ??? 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](https://en.wikipedia.org/wiki/Date_format_by_country) quite a few other places with USA and the Federated States of Micronesia being outliers. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [August 12, 2016, 11:50am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/16 "2016-08-12T11:50:42Z")
> [@Musicat](#): > > My ancient Excel, ca. 2000, has 16 ways of formatting a date cell. Doesn’t one of those work for you? 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. ---
**Author:** ![Keeve](https://avatars.discourse-cdn.com/v4/letter/k/f07891/32.png) [@Keeve](https://boards.straightdope.com/u/Keeve)\ **Post date:** [August 12, 2016, 11:58am UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/17 "2016-08-12T11:58:12Z")
> [@penultima\_thule](#): > > 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](https://en.wikipedia.org/wiki/Date_format_by_country) > quite a few other places with USA and the Federated States of Micronesia being outliers. 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”. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [August 12, 2016, 12:30pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/18 "2016-08-12T12:30:26Z")
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 ---
**Author:** ![Musicat](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/musicat/32/20189_2.png) [@Musicat](https://boards.straightdope.com/u/Musicat)\ **Post date:** [August 12, 2016, 1:42pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/19 "2016-08-12T13:42:24Z")
> [@Keeve](#): > > 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. 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”. ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [August 12, 2016, 2:08pm UTC](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399/20 "2016-08-12T14:08:07Z")
> [@Musicat](#): > > 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. [Next page](https://boards.straightdope.com/t/how-to-turn-off-excel-auto-correct-for-dates/710399.md?page=2)