Stop turning numbers into dates!

[zingzang]

I'm entering numeric values such as 1/5, 9/27 and 10/28, into an Excel
spreadsheet. Excels keeps trying to "help" by interpreting these
numbers as dates. So when I enter 1/5 (meaning "one out of five") it
changes the value to "January 5"!

They're not dates, despite Excel's insistance that they are. How can I
get Excel to stop "helping" me like this?

Thanks.

 

[Fredrik Wahlgren]

I'm entering numeric values such as 1/5, 9/27 and 10/28, into an Excel
spreadsheet. Excels keeps trying to "help" by interpreting these
numbers as dates. So when I enter 1/5 (meaning "one out of five") it
changes the value to "January 5"!

They're not dates, despite Excel's insistance that they are. How can I
get Excel to stop "helping" me like this?

Thanks.

Insert a hyphen before the text, i.e. '1/5 instead of 1/5

/Fredrik

 

[Paul Berkowitz]

Insert a hyphen before the text, i.e. '1/5 instead of 1/5

Fredrik means a single quote ' (apostrophe), as he demonstrated, not a
hyphen - .

This will format them as text, not numbers (nor dates), which is probably
what you want. . Or you can select the column (or row) where they'll be
appearing and go to Format/Cells.../Number, and choose either Fraction (up
to 2 decimal places) or as Text, depending on which you really want. If
you're not going to be doing numerical calculations on the fractions, choose
Text.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Bernard Rey]

Insert a hyphen before the text, i.e. '1/5 instead of 1/5

Or set the cell format to "Text" first.

 

[Bernard Rey]

Or set the cell format to "Text" first.

Ooops! Sorry, Paul, I hadn't seen your reply :-/

 

[Rob]

I'm entering numeric values such as 1/5, 9/27 and 10/28, into an Excel
spreadsheet. Excels keeps trying to "help" by interpreting these
numbers as dates. So when I enter 1/5 (meaning "one out of five") it
changes the value to "January 5"!

They're not dates, despite Excel's insistance that they are. How can I
get Excel to stop "helping" me like this?

Thanks.

Or enter 0 1/5 (zero - space - one - slash - five) if you need 1/5 as a
number in a calculation

Rob

 

[Fredrik Wahlgren]

Yes, Apostrophe is what I meant. Good thing I made an example.

/Fredrik

 

[Jim Gordon MVP]

Hi zingzang

If you want Excel to treat your entry as text, then put a single apostrophe
as the first thing you type into the cell. Like this:
'1/5

However, if you want Excel to treat your entry as a value (so you can use
the value in a cell formula or other calculation) then put a zero followed
by a space like this:
0 1/5
If you do it this way, the cell will display 1/5, but if you select the cell
and look in the formula bar you will see that Excel is treating it as a
decimal value of .2

-Jim

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.