CODEWITHSUNDEEP
dategoogle-sheets
Aaron Guild
Aaron Guild

Reputation: 13

Google Sheets, 8 digit number to date format

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!

Upvotes: 1

Views: 1749

Answers (1)

user4039065
user4039065

Reputation:

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

enter image description here

For , use this formula in an unused column,

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

enter image description here

Upvotes: 4

Related Questions