Table of Contents
Excel is a spreadsheet application that allows you to easily convert a YYYYMMDD format to a date format. To do this, select the cell containing the YYYYMMDD data and then click the “Number” format in the Home tab. From the list of available formats, select “Date” and the YYYYMMDD format will be automatically converted to a readable date format. This same process can be used to convert dates into YYYYMMDD 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.
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.