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.

**Additional Resources**

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

Excel: Calculate the Number of Months Between Dates

Excel: How to Calculate Sum by Date

Excel: How to Calculate Average by Date