Converting a custom date to readable text

C

Crossed eyes

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

Peo Sjoblom

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
 
S

SteveG

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
 
Top