how do I stop Excel taking decimal fractions as dates?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aa - 21 Oct 2006 22:35 GMT

sometimes when I enter decimal fractions Excel thinks this is a date. How do
I stop this?

Reply to this Message

Roger Govier - 21 Oct 2006 22:40 GMT

Hi

Insert a single quote before the fraction '3/4
The quote will not show up in the cell, but it will tell Excel to treat
the cell as Text.

Alternatively, Format>Cells>Number>Fractions
3/4 will display as 3/4 but will be stored as 0.75 and can be used in
further calculations.

Signature

Regards

Roger Govier

> sometimes when I enter decimal fractions Excel thinks this is a date.
> How do
> I stop this?

Reply to this Message

Ron Coderre - 21 Oct 2006 22:46 GMT

If you mean that when you type something like 1/4 Excel converts it to 4-Jan,

Try this:
Pre-format the input cells to "Number"

From the Excel main menu:
<format><cells><number tab>
Category: Number
Click [OK]

Now, when you input fractions...they convert to decimal numbers
e.g. 1/4 becomes 0.25

(If the format was "General" Excel takes its best guess at what you meant.
In the case of fractions, dates often result.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> sometimes when I enter decimal fractions Excel thinks this is a date. How do
> I stop this?

Reply to this Message

Ron Coderre - 21 Oct 2006 23:11 GMT

I forgot to mention....
if you want the fraction to remain a numeric fraction...

Either set the numeric format of the cell to "fraction"
OR
when entering the fraction, precede it with a zero and a space:
e.g. 0 1/4 would have a value of 0.25, but display as 1/4

***********
Regards,
Ron

XL2002, WinXP

> If you mean that when you type something like 1/4 Excel converts it to 4-Jan,
>
[quoted text clipped - 21 lines]
> > sometimes when I enter decimal fractions Excel thinks this is a date. How do
> > I stop this?

Reply to this Message

aa - 21 Oct 2006 23:24 GMT

Thanks.
I am talking about decimal fractions like 0.25 not 1/4
I am entering digits and therefore entering comma every time is a chore
Also how do I set this as a default in Excel once and forever so as when I
start Excel I do not need to do this formatting?

> sometimes when I enter decimal fractions Excel thinks this is a date. How do
> I stop this?

Reply to this Message

Ron Coderre - 21 Oct 2006 23:33 GMT

OK....now I need more details...

On my PC, if I type 0.25, that's exactly Excel puts in the cell and displays.

Exactly what to you type
and what does Excel convert it to?

***********
Regards,
Ron

XL2002, WinXP

> Thanks.
> I am talking about decimal fractions like 0.25 not 1/4
[quoted text clipped - 5 lines]
> do
> > I stop this?

Reply to this Message

aa - 22 Oct 2006 00:13 GMT

Hi, Ron, I think I now see what was the problem. On the computer I am using
the decimal separator is set to use comma (","). Therefore when I used dot,
it took it as a character string rather than a number.
Thanks for help

> OK....now I need more details...
>
[quoted text clipped - 18 lines]
> > do
> > > I stop this?

Reply to this Message

Rate this thread: