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: Newest
Most helpful Newest Oldest
  1. 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
  2. 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
  3. Anonymous
    2015-10-08T17:59:44+00:00

    I get the same problem with CAS numbers. Excel creates dangerous situations - it is not possible to have any vaguely "date-like, but not actually a date" data in Excel without it rapidly being corrupted if the file can be opened by users with multiple Excel versions. It's bad enough with just one.

    My latest challenge (Excel 2010):

    Format a cell as text

    Copy the text 31-08-9 into the cell from Word

    It is instantly 31/08/2009 the cell now shows as format date.

    Reformat the cell as text, and it now shows 40056

    Type the text 31-12-7 into a cell formatted as text and immediately Excel warns you that you have a date with a two digit year. Excel is determined to find dates and Excel having once decided something is a date your data is no longer safe.

    Conversely, type 31/12/1899 into a cell and ask Excel to display it in the form 13 March 2001 and Excel hasn't a clue what to do.

    So not only is Excel dangerous in its appetite for making non date data into dates it's also pretty inept at handling actual dates once it gets them!

    This is not Excel 0.9, this is supposed to be a mature software product.... Most Freeware authors would be embarrassed to be this bad!

    Was this answer helpful?

    0 comments