type in 4-7 changes to 38814

[Guest]

Hi, I was just curious. First, is this bug ever going to be fixed. Second,
how does 4-7 get interpretted as 38814 when switching from date format to
text format, and why? I cannot follow the logic, please help it is really
bugging me.

 

[JE McGimpsey]

Not a bug, it's a design feature...

XL needs some way of determining what type of entry you're making. The
input parser, by design, interprets two numbers separated by a hyphen,
as a date (as long as the result makes sense as a date: 123-456 will be
interpreted as Text). You're seeing the serial number of the date, which
is just an integer offset of 38814 from a base date (31 December 1899,
ignoring the fact that the 1900 system includes a non-date of 2/29/1900).


To keep 4-7 text, preformat your cell as Text, or prefix the entry with
an apostrophe.

 

[Peo Sjoblom]

It is no bug, just a stupid feature. Format as text or precede entry with an
apostrophe to tell Excel it is text, you can't change it after the
conversion is done
It interpreted 4-7 as 38814 days after Jan 0 1900 which is 04/07/06
Normally it is the current year which is 39179 days after Jan 0 1900 so you
might want to check your computer date. Dates in Excel is just days after
Jan 0 1900 (unless you use the 1904 date system) so if you put a date in any
cell and format it as number or general it will return a date serial number,
that is what you got. But you need to preformat as text or else it won't
work

 

[Guest]

One of Excel's "features" is that it converts anything that can be converted
to a date INTO a date.....even if that's not what you want.

In a cell not specifically formatted as Text,
entering 4-7 "should" return 39,179
......Which is the date serial number for 07-Apr-2007
However, you're getting 38,814?
......That's the date serial number for 07-Apr-2006
......Does your computer have the wrong year set?

To stop that on a cell-by-cell basis, set the cell's number format to Text.
OR
Prepend your value with an apostrophe:
'4-7

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

XL2002, WinXP