how can a date that is set up in excel as 19900301 be converted automatically
to read 03/01/1990?

clarice Wrote:
how can a date that is set up in excel as 19900301 be converted
automatically
to read 03/01/1990?
While you're waiting for an expert...here's an amateur's suggestion :)

On Fri, 3 Feb 2006 03:42:19 -0800, "clarice"
wrote:

how can a date that is set up in excel as 19900301 be converted automatically
to read 03/01/1990?

Method 1:

Select the cell(s)
Data/Text to Columns
Next
Next
Column Data Format
Date YMD (or YDM as appropriate)
Finish

Method 2 (formula):

=--TEXT(A1,"0000\/00\/00")

Format as Date

Note that the dates will be analyzed and parsed according to your local date
format.


--ron

If you want the answer to be in a date format and the string is in A1
one of the following will work. The date functions parameters are year,
month, day so it depends if you are displaying european or american
dates.

=DATE(LEFT(A1,4),RIGHT(A1,2),MID(A1,5,2))

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

If you format the date as dd-mmm-yyyy to start with, to check you have
selected the right formula, then use dd/mm/yyyy or mm/dd/yyyy are
appropriate

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=508040

Another way:

=VALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4))

and format as date.

Hope this helps.

Pete