I am currently working with a large spreadsheet that must be formatted as
"text" or "general" in its entirety, but birthdates that were submitted for
inclusion in the sheet are formatted as custom dates (yyyymmdd). Is there
any way to change the format for a column back into text without having the
dates appear as the serial value? (For instance, 19870917 appears as 32037
when the column format is changed to text or general.)

Please be kind in your responses...I don't have a great grasp on the
techno-language!

You can use a help column and a formula to get it to text

=TEXT(A1,"yyyymmdd")

copy down and then copy and paste special over the old dates

finally delete the help column, however I would personally use another date
format like

=TEXT(C1,"yyyy-mm-dd")

or

=TEXT(C1,"dd-mm-yyyy")

or

=TEXT(C1,"mm/dd/yyyy")

depending on the region


--

Regards,

Peo Sjoblom

"Crossed eyes" <Crossed (E-Mail Removed)> wrote in message
news:F1A39A20-A731-45B5-B7DA-(E-Mail Removed)...
> I am currently working with a large spreadsheet that must be formatted as
> "text" or "general" in its entirety, but birthdates that were submitted
for
> inclusion in the sheet are formatted as custom dates (yyyymmdd). Is there
> any way to change the format for a column back into text without having
the
> dates appear as the serial value? (For instance, 19870917 appears as
32037
> when the column format is changed to text or general.)
>
> Please be kind in your responses...I don't have a great grasp on the
> techno-language!

You could convert the custom format to Text first and then paste it in
the column. Insert a column next to the list of custom dates. In the
column to the right enter in:

=TEXT(A1,"yyyymmdd")


This will return the date in A1.

Drag this down your list, copy and paste Special Values over the custom
dates. Delete the row where you did your conversion.


Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=496740