30977 is the date in a Number format. If you change the format of the cell
back to Date, it will show correctly without having to re-type.

If you paste special using Text as your format, you have a much better
chance of the cell retaining the format in your current file.

but, at worst, you can merely highlight your date column and change the
format using FormatCells.

If you are pasting from another Excel file, if possible, open from file at
top left of your current workbook. This way, when you go to Paste Sepcial,
you can select Values.

"frustrated worker" wrote:

I am having problems getting Excel to retain the formatting for a column.

When I realize its reformatting my data into a default date format, if i
change the format setting on the column/cell back to "text", it produces some
type of equation result (i.e. 30977 instead of 12/26/2006). Currently I have
to undo the action, and change each cell format individually and then retype
in my data.

I initially set the column to format "Text" so that it doesn't change the
format of my dates. The column contains dates in about three different
formats, and i need to retain the original format entered. It does fine
retaining the format, if i am directly entering data. but has problems if
its from a pasted source, or was directly entered prior to changing the
column format to "text.

1. is there a way to reformat the cell to text without the cell data being
changed?

2. When the cell data has been changed (i.e. 30977 instead of 12/26/2006),
is there a way to get back my originally entered data without re-entering the
data?

3. This problem is exacerbated when i have to paste in my data (often from a
Word document) either from another Excel document or a Word document - is
there anyway to prevent the column from reformatting itself on a paste in?