cell formatting ends up scientific?

[George Applegate]

I have a question,

Sometimes when we import data from a comma separated file it will take
a date field, say 8/24/06 and display it in a scientific format - like
3.90E+04. Why does it do this? Is there a way to add a spreadsheet
function to reformat a column after the import takes place to get it
back to a date format? Or I think it's done it when the field is
082406 or 08-24-06. Or it could be 2007-01-09 or 2007/01/09, etc.

I think it does this even if you have predefined the column as "text"
or "general" or even "date".

I guess I have a hard time understanding why excel tries to outsmart
me and how I can outsmart it instead.

Thanks for any suggestions.
ga

George Applegate
(e-mail address removed)

 

[Peo Sjoblom]

If you change the file extension to *.txt the text import wizard will start
and if you click next twice and select Date under column date format and the
format of the imported date (not your default date format) so if the date is
082406 select MDY, if it is 2007-01-09 or 20070109 select YMD then it should
work fine. Note that you don't have to rename the cell if you do a bit of a
detour, assume you have saved the file on your hd and it is called *.CSV

do data>import external data>import date and type *.* in the filename box,
that will show all files, now find your *.CSV file and open it and the text
import wizard will open