how to convert 19900301 to 03/01/1990

[Guest]

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 converte
automatically
to read 03/01/1990?

While you're waiting for an expert...here's an amateur's suggestion :

 

[Ron Rosenfeld]

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 A
one of the following will work. The date functions parameters are year
month, day so it depends if you are displaying european or america
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 hav
selected the right formula, then use dd/mm/yyyy or mm/dd/yyyy ar
appropriate

Regards

Da

 

[Pete]

Another way:

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

and format as date.

Hope this helps.

Pete