enable/disable auto-convert a NUMBER to DATE

[Harry Nopter]

When I insert a decimal number (e.g. 3.8) in a cell, then it is automatically
shown as a date (e.g. 3-Aug-09), even if I put the number's format (at "cell
format") in the number or in the general format option. I have the 2003
Office. How can I disable this automatic feature?

 

[Mike H]

Hi,

I'm a bit surprised that Excel is interpreting 3.8 as a date but if it is
the only way i can replicate it is if somehow you have managed to get an
autocurrect option set for changing . to / so try this.

Tools|Autocorrect options

Type . in the replace box and if anything comes up in the replace-with box
delete the entry.

Mike

 

[Harry Nopter]

I appreciate your effort but nothing comes up in the replace-with box! My
question shouldn't surprise you. I found a few people having exactly the same
question with me while I was trying to find an answer by searching in forums,
e.g. :

http://www.computing.net/answers/office/disable-excel-cell-auto-correct/5180.html
http://geekswithblogs.net/Patware/archive/2005/10/11/56647.aspx
http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2008-11/msg01106.html
http://www.mrexcel.com/forum/showthread.php?t=251103

But no answer is correct. The thing is that I did exactly the same task in
another computer and I had no problem! 3.8 was not converted to 3-Aug-09. It
was recognized as a number. How can I disable this feature?

 

[David Biddulph]

Check the number and date settings in Windows Regional Options (in Control
Panel). I think you'll find that those settings differ between your two
machines.

Format Cells affects how a number is *displayed* in Excel once it has been
stored, but it is Windows Regional Options that governs how the number is
interpreted when you type it in.

 

[Harry Nopter]

Dear David, Thank you! Here is a sum up:

- THE ANSWER - THE ANSWER - THE ANSWER -

-So mainly the correct answer sounds like this:
You cannot disable this Excel's feature but you can change the Window's
setting according to what you want Excel to be displaying!

-How to do this?
"Control Panel" (-> "Switch to Classic View") -> "Regional and Language
Options" -> tab "Regional Options" -> "Customize..." -> tab "Numbers" -> And
then change the symbols according to what you want.

I want to point out that someone told me to change the cell's format to
"Text" format. This works partially, because while you can see your data as
you have written them, although when you import these data to other software
like statistical packages, then it might recognise your data as text data
(string varaible) instead of numeric data (numeric variable). So the correct
ansewer is not just to "Text" format your cells, except if that is ok with
your needs.

Thank you all & especially a great thanks to David Biddulph.