Problem entering dashes in certain spots

  • Thread starter Thread starter WorkingHardly
  • Start date Start date
W

WorkingHardly

I have a problem where sometimes when I try to enter a number in the format:
1-4-1 in a cell, it automatically enters different number such as 35639. I
cannot find any hidden formulas, or conditions, and it doesn't happen in
every cell.Anyone have any ideas about what could be causing this?
 
Excel thinks you are entering dates and is converting to the serial number
for that date. 35639 is 7/28/1997 for example.
36895 would display for a cell where you typed 1-4-1 and excel coverted to a
date.

Format the cells as text before you enter, to stop it from happening
or you can begin the cell entry with an apostrophe:
'1-4-1
which is just another way to format a cell as text. The apostrophe will not
display in the cell.
 
Excel is...ahem..."helping". It assumes you intended to enter a date, so it
converts your date-like input to an Excel date-serial number.

To Excel, each date is represented by the number of days the date is after
31-DEC-1899.
For example:
01-JAN-1900 is date-serial 1
11-MAR-2008 is date-serial 39,518

To avoid the conversion:
1) Enter an apostrophe (') before the entered value: '1-4-1
or
2) Format the input cells as TEXT
From the Excel Main Menu:
<format><cells><number tab>
Category: Text

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Excel has translated your 1-4-1 to a date of 1/4/2001 (which it stores as
36982 or 36895, depending on whether it interprets it as 1st April or 4th
January). If you want to keep the data as 1-4-1, either format the cell as
text before you type the data in, or prefix the data with an apostrophe.
 
That worked guys, thanks for all your help on this. I cannot tell you how
annoying that was. Thanks!
 
Back
Top