# Excel: How to Convert YYYYMMDD to Date Format

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.

Thus, if we type DATE(2019, 10, 30) then Excel 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 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.