how to convert 19900301 to 03/01/1990

[clarice]

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

[Timmy Mac1]

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 .

Insert another column or row and (assuming the original is in cell A1) use the formula...

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

[Ron Rosenfeld]

On Fri, 3 Feb 2006 03:42:19 -0800, "clarice"
<[email protected]> 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

[Dav]

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

[Pete]

Another way:

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

and format as date.

Hope this helps.

Pete