Excel- Changing Dates to Text format 2

[SellOut]

Hello,

I have an excel sheet that I am trying to import into Access. Two columns in the sheet have some cells that are formatted to date. This creates problems when importing into Access because it suggests the field should be Date/time when I want it to be text. I have been trying to copy and paste special the column so that text cells stay the same and date cells appear as dates in appearence but in text format.

My question is how can I copy and paste the column back as it appears (eg. 01/05/2005) in text format rather than the date value number that it has (eg. 31586).

Thanks for any help,

SO

 

[byundt]

Try a formula like this in a separate column:
=TEXT(A1,"mm/dd/yyyy")
It will format numbers (and dates) as mm/dd/yyyy and convert them to text. Existing text will be left unchanged. You can then copy this column and Edit...Paste Special...Values on top of the original.

 

[SkipVought]

SO,

Format the column as TEXT

Add a column...
[tt]
=text(A2,"mm/dd/yyyy")
[/tt]

Copy the column with the formulas and Paste Special - VALUES into the column formatted TEXT

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}