Converting a custom date to readable text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Crossed eyes - 29 Dec 2005 18:51 GMT

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! :)

Reply to this Message

Peo Sjoblom - 29 Dec 2005 19:12 GMT

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

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 5 lines]
> Please be kind in your responses...I don't have a great grasp on the
> techno-language! :)

Reply to this Message

SteveG - 29 Dec 2005 19:26 GMT

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

Signature

SteveG

Reply to this Message