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