retain custom date format "text" when pasting or reformatting data

[frustrated worker]

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?

 

[Sean Timmons]

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 Format>Cells.

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.

 

[Gord Dibben]

When you change formatting to "Text" what you see is the serial number of
the date..............that's how Excel interprets a date..........as a
serial number starting at 1 for January 1, 1900

To get your dates back, re-format to date.

Why do you need 3 different formats for dates in the column?

There is no way to format to Text after the fact and retain the original
date format........they will always revert to serial numbers.

If the cells are pre-formatted to Text, they will retain the formatting
since you are entering the dates as Text.

When pasting from another source make sure you have the "Paste Options"
enabled.

Then select "keep destination formatting" from the "Paste Options" dropdown
menu.


Gord Dibben MS Excel MVP