bug - type 1-4531 in any cell - returned Jan-51 workaround is '1-.

[Guest]

In Excel office 2k if you type 1-4351 in any cell the returned value is
Jan-51, 2-4531 =Feb-51 etc. you cannot change the format of the cell to
display numbers!!! The workaround for this bug is to put an open quote mark
in front of the number i.e. '1-4531, as this is the type of number I use
regularly it is extremely annoying, has this already been reported and if so
is there a fix for it?

 

[Guest]

Hi

Set the cell format to Text before you type into the cell.

 

[Guest]

this is not a 'bug' as Excel is programmed to auto interpret numbers that
look like date values as Dates.

A work around is to format your cells as Text prior to entering data in them.

HTH

Bruce

 

[Gord Dibben]

Paidi

Not really a bug, just the way Excel deals with data in that format.

Excel assumes it is a date being entered.

Try pre-formatting the cells to Text then enter the data.

1-4531 is not a number, it is text.


Gord Dibben Excel MVP

 

[Myrna Larson]

Excel is programmed to auto interpret numbers that look like date values as
Dates.

True, but 1-4531 and 2-4531 don't "look like" dates to me. How would you
describe the format?

 

[Guest]

If you enter 1-2005, Excel assumes Jan 1, 2005 and returns Jan-05, hence, it
treats 1-4351 as Jan 1, 4351 and returns Jan-51

I agree, it doesn't look like a date to you and me... but Excel often
doesn't think like you and me! Ironically, if you enter 1-1899 it returns a
text entry "1-1899"; as numbers prior to 1900 are not treated as dates!

Cheers!

Bruce

 

[Myrna Larson]

Of course! I had forgotten that Excel's calendar now goes to the year 9999.