Table of Contents
To convert the YYYYMMDD format to a date format in Google Sheets, you can use the DATE function. This function takes three arguments: year, month, and day. You can extract the year, month, and day values from the YYYYMMDD format using the LEFT, MID, and RIGHT functions respectively. Then, use the DATE function to combine these values and convert them into a date format that can be easily read and manipulated in Google Sheets.
You can use the following formula to convert a date in YYYYMMDD format to a DD/MM/YYYY format in Google Sheets:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
This particular formula converts the date value in cell A2 from a YYYYMMDD format to a DD/MM/YYYY format.
For example, this would convert a value of 20191030 to 10/30/2019.
This is a much easier date format to read.
The following example shows how to use this formula in practice.
Example: Convert YYYYMMDD to Date Format in Google Sheets
Suppose we have the following column of dates in Google Sheets that are currently formatted as YYYYMMDD:
We can type the following formula into cell B2 to convert the date value in cell A2 to a more recognizable date format:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
We can then drag and fill this formula down to each remaining cell in column B:
Notice that each date value in column A has been converted to a date value with a MM/DD/YYYY format in column B.
How This Formula Works
The DATE function in Google Sheets uses the following basic syntax:
=DATE(year, month, day)
Thus, if we type DATE(2019, 10, 30) then Google Sheets will return 10/30/2019.
Now consider when we use the following formula:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
This formula tells Google Sheets to provide the following arguments to the DATE function:
- The first 4 characters on the left of the string.
- The middle 2 characters (starting from position 5) of the string.
- The last 2 characters on the right of the string.
Thus, a date formatted as YYYYMMDD gets converted to:
=DATE(YYYY, MM, DD)
This produces a date value with a MM/DD/YYYY format.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: