---

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: