How to Convert YYYYMMDD Text String to Date Format

When working with data extracted from the data server, you have a large chance to get the date in the "YYYYMMDD" text format.

To convert the "YYYYMMDD" text string into the regular data format, you need to extract the year, the month and the day first, then use the DATE Function.

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

For example, the date string 20180507 will return 5/7/2018.

If you need to return the format of 2008-05-07, please use the formula below:

=LEFT(A2,4) & "-" & TEXT(MID(A2,5,2), "00") & "-" & TEXT(RIGHT(A2,2), "00")

– To extract Year from 20180507: LEFT(A2,4) returns 2018

– To extract Month from 20180507: TEXT(MID(A2,5,2),"00") returns 05

– To extract Day from 20180507: TEXT(RIGHT(A2,2),"00") returns 07

If you have another 8 letters date string such as 05072018 ("mmddyyyy"), you can use the same logic to separate the string into the year, month, day, and use the Date Function to convert it into the date format.

In case you need to convert the dates into different date formats, please combine with the Text Function. For example, to convert the date string "20180507" to "May 07, 2018", please use the following formula or check how to convert a serial number into a date:

=TEXT(DATE(LEFT(A2, 4), MID(A2,5,2), RIGHT(A2,2)), "MMM DD,  YYYY")