This cell contains a date string rep. with only 2 digits for the y

J

jdungan

I'm creating a spreadsheet of Test Cases using 1-1, 1-2, etc as my numbering
system.

The cells are all set as Text format, yet I started getting the following
error in Excel when I get to 1-32 and up:

"This cell contains a date string represented with only 2 digits for the year"

These cells are formatted to text so that they appear "as typed" so why is
it trying to convert it into a date?

I also can't use bulk replace to change "1-" to "2-"; when it does the
replace it converts ALL my test case numbers to (as far as I can tell) random
5 digit numbers; ex. 1-1 to 2-1 changes automatically to 39845, as well as
gives the following error:

"The number in this cell is formatted as text or is preceded by an
apostrophe".

This is extremely annoying and also means that I have to go through all the
duplicated test case sheet and change all the numbers manually.

Any suggestions?
Thanks.
 
G

Gary''s Student

You need to adjust your error check options:

Tools > Options > Error Checking

or in 2007:

BIGBALL > Excel Options > Formulas > Error checking rules.
 
J

jdungan

Thanks, but that only stops error messages, it doesn't stop Excel from
changing my test case numbers to dates/5 digit numbers when it shouldn't be
touching them at all.
 
T

TK

Hi,

When you type "1-1, 1-2..." in excel, it treats it as Date value. Since you
set the formatting as "Text", internally it's still date, but shows as text.
To really make this as text, just prefix your value "1-1" to " '1-1" so that
excel knows that this is a text and not date. The only problem is that you
will see a small green triangle at the upper left corner, which can be
ignored.

Try this and let me know...
 
J

jdungan

Thanks,

That works. Hopefully though I won't be writing more test cases once they
get a team into the office for that, and I can do my real job :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top