Google Sheets, 8 digit number to date format

Question

I am using Google Sheets to import a list of names and dates from a comma delimited text file. It imports fine but when I try to convert the date cells I get strange results. The dates are 8 digits long and arranged "year, month, day". for example, i have this date. 20170425 when I format it to any of the date formats I get 57124-09-13.

I've tried everything I can think of and searched forever for an answer.

Thanks so much in advance to anyone who can steer me in the right direction!

Answer 1

Since you've added an Excel tag, use Data, Text to Columns, Fixed Width, Next, Date: YMD, Finish.

For google-sheets, use this formula in an unused column,

=DATEVALUE(replace(replace(A1, 7, 0, "/"), 5, 0, "/"))