Stop auto correction of number into a date

Anonymous
2010-04-09T11:08:55+00:00

When i work with excel, i do data entry, some of the numbers that i type in can be interpenetrated  as dates. when this happens excel automatically changes the format of the cell from general, to date. i want to be able to stop this auto correction.

I know there are a number of ways to avoid this happening, but i am fed up with these methods. i simply what to turn the auto correct off, so that instead of what i type into the cell changing, what i type into the cell shows exactly what i typed in.

really hope there is someone out the who knows how to do this.

thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-04-09T17:09:58+00:00

If you format the cells as Text, all entries will be entered as text

(leading zeroes will be retain, date-like values won't be converted to dates, etc)

...CTRL+1 (to view the formatting dialog window)

...Select: Number_tab...Category: Text

That should resolve most (but not all) of the problem.

Does that work for you?

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

Was this answer helpful?

60+ people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-04-09T16:55:47+00:00

Tim

I understand your frustration but this is not an autocorrect issue. 

Typing in 1/5 will automatically be recognised as a date as that is what it is unless... you signify that it is a formula and you expect the answer 0.2 to appear or it is a text item in which case you must preceed the data with a ' apostrophy.

In signifying the that the item is text you will have a data item which is left aligned, as all text is by default.  All other items in this column (even if purely numeric such as product codes) should aligned left.  Product codes though numeric should never be treaded like numbers, if you do, then expect issues.  Telephone numbers are similarly not true numbers, if they were treated as such, leading zeros would be dropped.

Though the whole idea of having to type in that extra character of ' is a pain to remember, it will make your data more acurate, reliable and portable.

Pat PS If you found this useful please vote. Thank you:¬)

Was this answer helpful?

40+ people found this answer helpful.
0 comments

311 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2015-10-15T09:53:56+00:00

    This is really starting to get "annoying". Now gene names are also being converted into dates. Ok, I do see that the gene name "SEPT7" does look very much like a date, no argument there. So Excel automatically turns it into "Sep-07". But how on earth does "Sep-07" become "39326" when the cell is formatted to text??? Unless Microsoft has their own calender which started exactly 107 years ago, this makes no sense whatsoever.

    At least keep the original content saved in the background so that it can be recovered.

    Could someone from Microsoft actually give us a reply here? Because thus far we are all just griping about a (partially) unsolvable problem. I thought the whole purpose of this place was to get answers from the evil overlords at MS???

    This behaviour just proves that the people at MS are not only lazy and incompetent, but also rude. But I guess this is what we can expect from a company that cares more about the aesthetics of their products than they do about the quality.

    Let me tell you this, your product does definitely NOT "excel".

    Disappointing.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-10-15T10:41:28+00:00

    As is well documented, Excel stores dates as serial numbers with 1 = 1/1/1900.  So, yes, Excel's calendar does start 107 years prior to Sep 2007.

    And Excel does not store the original value.  The only work-around is to have the cell formatted as Text BEFORE you enter a value that looks like a date.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-10-15T12:24:12+00:00

    That's all nice and good if what you want to do is store the sales history of your frozen banana stand, but it is not a solution if you are working with biological analysis data. Our high throughput analysis softwares generate .txt and .csv files with thousands of rows of numerical data. Working with the tables is often more convenient in Excel, but since the BEFORE that you are referring to, Ron, does not exist, due to the fact that the files are automatically generated, the cells cannot be formatted to text BEFORE!

    Now it is of course possible to open a blank spreadsheet, format all cells to text, copy all data over to the new file from the .txt or .csv, in which case the formatting error does not occur. But the file needs to be saves to .txt or .csv again for the next analysis step, and if the file is now opened with Excel again, the auto-formatting will re-occur, since .txt and .csv files do not store cell formatting. And since these files are opened, modified and re-saved many times a day every day, following this procedure every time is extremely inconvenient and a waste of valuable company time.

    So, my question is this. Since turning of the auto formatting used to be an option in former versions of Excel, why are we given half-baked work-arounds that do not work for us (and anybody working with large sets of data, be it biology, physics, finance, medicine...), instead of just adding the option to turn off auto formatting in one of the many regular updates?

    And why is this auto formatting active by default in the first place, when it is actually more likely that a number will be converted to a date by mistake, rather than by design? In addition, if I want to write a date, I will write the date, and not a random combination of characters resembling a date. And from an earlier post from a different user, it has become clear that the auto formatting of dates is likely to NOT RECOGNIZE ACTUAL DATES! (Three posts up, posted by TonyCorless).

    So bottom line, this auto formatting is in the way of people working with large data, and not that useful for people who want to write dates, since it is safer to just write the date correctly.

    Any comments or suggestions?

    Was this answer helpful?

    0 comments