mmm-yy issue

[tarku]

Hi there,

How do you get excel to see a number written in a box as a number, and not
in mmm-yy format as default. For example, when I open excel as a new sheet, I
will type 2000-10, and it will auto format as oct-00, and in the tool bar it
says 2000-10-01, when all I want is it to show 2000-10, and not any other
format.

I have been using a temperary solution of selecting all the cells and going
to format cells > text to achieve this result, but excel doesn't save the
setting so when I reopen the workbooks after entirely closing excel. I need
to find a final solution, since it is for my work, and I am no longer working
at my old company, so my replacements doesn't have to worry about it.

I have 2 specific workbooks I constantly use, and I always create a new one
for temp. data for the day, and this affects all workbooks, regardless if the
formula is locked or not, until I format cells, then it carries over.

Thanks for any assistance

-John

 

[David Heaton]

Hi there,

How do you get excel to see a number written in a box as a number, and not
in mmm-yy format as default. For example, when I open excel as a new sheet, I
will type 2000-10, and it will auto format as oct-00, and in the tool barit
says 2000-10-01, when all I want is it to show 2000-10, and not any other
format.

I have been using a temperary solution of selecting all the cells and going
to format cells > text to achieve this result, but excel doesn't save the
setting so when I reopen the workbooks after entirely closing excel. I need
to find a final solution, since it is for my work, and I am no longer working
at my old company, so my replacements doesn't have to worry about it.

I have 2 specific workbooks I constantly use, and I always create a new one
for temp. data for the day, and this affects all workbooks, regardless ifthe
formula is locked or not, until I format cells, then it carries over.

Thanks for any assistance

-John

John,

formatting the column before you enter the data should fix the
problem, even after saving. however you could also try this.

type an apostrophe before the number so
instead of 2000-10
enter

'2000-10

hth

Regards

David

 

[tarku]

Sadly I can't type '2000-10 due to the fact that that particular number is a
product code, which is a paint colour for Benjamin Moore. Weirdly when I save
the spreadsheet, that is the only thing that doesn't save. It started to
happen right after I got a nasty virus, and I had to clean everything, and
reinstall excel. It's bloody annoying, and worst case will be I will have to
train the new people to set it up everytime.

Thanks for the reply though!

-John

 

[David Heaton]

Sadly I can't type '2000-10 due to the fact that that particular number is a
product code, which is a paint colour for Benjamin Moore. Weirdly when I save
the spreadsheet, that is the only thing that doesn't save. It started to
happen right after I got a nasty virus, and I had to clean everything, and
reinstall excel. It's bloody annoying, and worst case will be I will haveto
train the new people to set it up everytime.

Thanks for the reply though!

-John






- Show quoted text -

John,

The apostrophe disappears after you hit enter. it only serve as a way
of telling excel that you want the data to be text.
if you have a product code that starts with an apostrophe then you
would need to enter the apostrophe twice.

Regards

David

 

[norie]

John

So why don't you just format the cells before you enter anything in
them as avid suggested.

The most appropriate format would be Text.