You can use the following formula to convert a date in YYYYMMDD format to a DD/MM/YYYY format in Excel:

=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, which is an easier date format to read.

The following example shows how to use this formula in practice.

Example: Convert YYYYMMDD to Date Format in Excel

Suppose we have the following list of dates in Excel 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.

Bonus: How This Formula Works

The DATE function in Excel uses the following basic syntax:

=DATE(year, month, day)

It then returns a date with a MM/DD/YYYY format.

Now consider when we use the following formula:

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

This formula tells Excel to provide the following arguments to the DATE function:

• The first 4 characters on the left of some string.
• The middle 2 characters (starting from position 5) of some string.
• The last 2 characters on the right of some 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.

The following tutorials explain how to perform other common tasks in Excel: