dateformat in text

[ericlch16]

I have a column which is a text format which is

ddmmyyyy. I want a formula to convert it to yyyymmdd.

when i download data from an ERP, the field is a text as opposed to date format.

any suggestion for a formula that i put in another column to convert it?

 

[VoG]

Try

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

and format as required.

 

[ericlch16]

dd/mm/yyyy
15/04/2011

with
=DATE(MID(A2,4,2),LEFT(A2,2),RIGHT(A2,4))

returns
September 1, 1910 ===> i can change the date format later to mm/dd/yyyy but the serial numbers are wrong. it should be April, 15 2011

 

[VoG]

Try

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

 

[JB_Scotland]

If your date is formatted as text dd/mm/yyyy then use =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

If it is text as ddmmyyyy as you first suggested VoG's formula works.

Edit VoG types faster than I do!