Reply
Thread Tools Display Modes
#1
Old 01-20-2015, 12:34 PM
BANNED
Join Date: Aug 2013
Posts: 1,481
How to turn OFF Excel auto correct for dates ?

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
#2
Old 01-20-2015, 12:39 PM
Guest
Join Date: Jul 2000
Location: socal
Posts: 576
Select all the date cells you've entered, Format Cells - > Number -> go down to Date and select the date format you want.
#3
Old 01-20-2015, 12:40 PM
Charter Member
Join Date: Sep 2003
Location: Southeast Florida USA
Posts: 21,035
Which version of Excel?
#4
Old 01-20-2015, 12:51 PM
Guest
Join Date: Feb 2009
Location: Edinburgh
Posts: 132
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.
#5
Old 01-20-2015, 01:06 PM
Charter Member
Join Date: Dec 2002
Location: Very east of Foggybog, WI
Posts: 4,804
Quote:
Originally Posted by Lungfish View Post
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.
#6
Old 01-20-2015, 01:18 PM
Guest
Join Date: Aug 2005
Location: Ottawa
Posts: 23,198
Well, I'm confused. Formatting the cell as text turns it off. It's always been able to turn it off.
#7
Old 01-21-2015, 09:21 AM
Guest
Join Date: Aug 2005
Location: Ottawa
Posts: 23,198
No update?
#8
Old 01-21-2015, 10:31 AM
Guest
Join Date: Jul 2012
Posts: 314
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.
#9
Old 08-11-2016, 03:15 PM
Guest
Join Date: Aug 2016
Posts: 1
Excel 2010

So instead of 1 - 2 creating Jan-2 I now get -1. Not quite what I wanted...

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.

Last edited by SysGW; 08-11-2016 at 03:16 PM.
#10
Old 08-11-2016, 06:04 PM
Guest
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,079
Quote:
Originally Posted by davida03801 View Post
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]
#11
Old 08-11-2016, 06:26 PM
Arbitrary and Capricious
Moderator
Join Date: Jul 2003
Posts: 9,098
Even in zombies, it still can't be done:
Quote:
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)
#12
Old 08-11-2016, 09:24 PM
Guest
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,079
Quote:
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.
#13
Old 08-11-2016, 09:42 PM
Member
Join Date: Apr 2000
Location: Flatlander in NH
Posts: 25,528
Quote:
Originally Posted by SysGW View Post
So instead of 1 - 2 creating Jan-2 I now get -1. Not quite what I wanted...

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.
#14
Old 08-11-2016, 09:56 PM
Charter Member
Join Date: Oct 1999
Location: Sturgeon Bay, WI USA
Posts: 20,401
Quote:
Originally Posted by Keeve View Post
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?
#15
Old 08-11-2016, 10:10 PM
Guest
Join Date: Apr 2009
Location: Sydney, Australia
Posts: 2,477
Quote:
Originally Posted by Keeve View Post
. 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/YYY y is the standard date format here and according to Wiki https://en.wikipedia.org/wiki/Date_format_by_country
quite a few other places with USA and the Federated States of Micronesia being outliers.
#16
Old 08-12-2016, 06:50 AM
Guest
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,079
Quote:
Originally Posted by Musicat View Post
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.
#17
Old 08-12-2016, 06:58 AM
Guest
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,079
Quote:
Originally Posted by penultima thule View Post
Yep.

Most likely spoken as 11th August.

Aug-11 would be taken as meaning August 2011 as dd/mm/YYY y is the standard date format here and according to Wiki 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".
#18
Old 08-12-2016, 07:30 AM
Guest
Join Date: Apr 2009
Location: Sydney, Australia
Posts: 2,477
Any Excel user from dd/mm/YYY y 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

Last edited by penultima thule; 08-12-2016 at 07:31 AM.
#19
Old 08-12-2016, 08:42 AM
Charter Member
Join Date: Oct 1999
Location: Sturgeon Bay, WI USA
Posts: 20,401
Quote:
Originally Posted by Keeve View Post
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".
#20
Old 08-12-2016, 09:08 AM
Charter Member
Join Date: May 2000
Location: SW Side, Chicago
Posts: 44,414
Quote:
Originally Posted by Musicat View Post
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.
#21
Old 08-12-2016, 09:44 AM
Member
Join Date: May 2001
Location: Sydney, Australia
Posts: 17,918
At work we run hundreds of automated jobs that extract data using various varieties of SQL and pump the data into Excel spreadsheets for distribution to the relevant work areas. For any that require particular formatting we simply create a template for the report. Only has to be done once and for all eternity every subsequent report looks the same.
#22
Old 08-12-2016, 10:56 AM
Charter Member
Join Date: Oct 1999
Location: Sturgeon Bay, WI USA
Posts: 20,401
Quote:
Originally Posted by pulykamell View Post
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.
Here's a workaround: Create a new file. Set the defaults as you wish, and save the file. When you start a new project, open this file and save it under your new project's name. Repeat as needed.
#23
Old 08-12-2016, 11:49 AM
Charter Member
Join Date: May 2000
Location: SW Side, Chicago
Posts: 44,414
Quote:
Originally Posted by Musicat View Post
Here's a workaround: Create a new file. Set the defaults as you wish, and save the file. When you start a new project, open this file and save it under your new project's name. Repeat as needed.
It's never been something that bugged me, but I can see how another user might find it odd.
#24
Old 03-12-2018, 03:45 AM
Guest
Join Date: Mar 2018
Posts: 1
Format the column as Text, then use the following VBA

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim vCount
If Target.Cells.Count > 1 Then Exit Sub

With Target
vCount = Len(.Value) - Len(Replace(.Value, "/", ""))

If vCount > 1 Then
Application.EnableEvents = False
.NumberFormat = "d/M/YYY y"
Application.EnableEvents = True
End If
End With
End Sub
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 10:50 AM.

Send questions for Cecil Adams to: [email protected]

Send comments about this website to:

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright 2018 STM Reader, LLC.

Copyright © 2017
Best Topics: new beard itch small maggots xkcd isn't funny pineapple vodka drink bobos shoes wrong zip code mystic river ending batteries white stuff green rooster beer both tables camping ground cloth 138 meaning rated r movies with nudity devry vs university of phoenix can you buy smarties in the usa does walgreens refill printer ink what does redbull taste like how to remove gas from a lawn mower what to do with extra frosting dog wetting bed but not urine how to get toilet to flush stronger silver cup for baby tradition difference between registered mail and certified mail