string of numbers to a date

[John Ford]

I have a string of numbers as you can see on the provided attachment, that in the columns labeled date of rank, and oer ncoer date, show as yyyymmdd. I need these numbers to show as mm/dd/yyyy, and cannot for the life of me get the formula right to do that. I need to create a blank column, and create the formula to convert the numbers to the desired date format, and then copy and paste special values only to the newly created column. please help.

[daffodil11]

E2:

=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(LEFT(C2,8),2))

Copy down and over as needed.

Paste those values into C2

The tricky part for me is that there are proceeding blank values after the string, and they aren't spaces.

[Jonmo1]

Try

=TEXT(LEFT(C2,8),"0000-00-00")+0

Then format the cell with the formula in a date format of your choice.

[John Ford]

Thanks daffodil, that is exactly what I needed.

[martindwilson]

select column c
then data /text to columns
click next twice
at the bottom you will see two columns labelled general
click in the first one
above you have some options
select date
select ymd
click the second column
choose do not import column (skip)
click finish
repeat for column d
format both columns as date mm/dd/yyyy