Unwanted automatic number-to-date conversion

H

happyperson

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have been trying to type a win-loss record (e.g. 4-0) in excel but every time I type it excel automatically converts it to a date (4-0 goes to Apr-00). How can I change this unwanted autocorrect option*.

* I tried using autocorrect to replace 4-0 with 4-0, but it didn't work.
 
N

Niek Otten

Precede the entry with an apostrophe. That will not show in the cell.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Version: 2008
| Operating System: Mac OS X 10.5 (Leopard)
| Processor: intel
|
| I have been trying to type a win-loss record (e.g. 4-0) in excel but every time I type it excel automatically converts it to a
date (4-0 goes to Apr-00). How can I change this unwanted autocorrect option*.
|
| * I tried using autocorrect to replace 4-0 with 4-0, but it didn't work.
 
C

CyberTaz

In addition to Nick's suggestion you can also select the cells *first* and
use Format>Cells - Text, then type in the info. But the cause of your
problem is that you are "going against the grain" in terms of what Excel
*expects* you to do. IOW, 4-0 isn't looked at as a number, as a text string
or as an operation - it's seen as a Date Value.

Excel expects you to put the "win" values in one cell and put the "losses"
value in the adjacent cell. I'm not saying you're wrong to do it as you are,
but if you ever intend to do any calculations - such as totaling wins &
losses, averages, etc. you're bound to run into problems if both values are
stored in the same cell & the cell is formatted as Text. At the very least
it will be much more complicated - if not impossible - to derive any
calculated results.

HTH |:>)
Bob Jones
[MVP] Office:Mac
 
C

Carl Witthoft

There was some buzz about this sort of counterproductive "helpfulness"
at /. or somewhere similar recently. I found a reference to an article
from a few years back where a bunch of genetic sequence researchers got
royally screwed by Excel due to this bug. Seems some genes have names
like SEPT-2 and you can just guess what happened to that entry in the
sequence :-(
 
C

CyberTaz

Understood - technically it isn't a"bug" - it's "by design" - but it's one
of those *intuitive features* that are a real PITA to contradict when the
need arises. They've made it so virtually impossible to enter a date
incorrectly that it's equally impossible to enter anything that remotely
resembles a date & have it *not* be seen as one:-(

....Didn't say it was *right*, just that that's how it *is*:)

Regards |:>)
Bob Jones
[MVP] Office:Mac
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top