Convert yyyymmdd to date

There are at least two ways to convert yyyymmdd to date – a formula and text to columns wizard.

or with only DATE and MID functions.

What you have to remember is that in order to use this function, you must have consistent data. This means that a year always consists of four digits, month uses two, and days also uses two.

Let’s look at how this formula works in the following example.

Formatting date

Now, you can select cells from B1 to B10, right click and choose format cells.

A new window will appear. Now, choose Date and select the type of formatting you want.

Text to column Wizard

Alternatively, you can use Text to Columns Wizard. This time it won’t be converted to date, but to year, month and day in the separate columns.

Select the data and choose Text to Columns button. It is located in Data >> Data Tools >> Text to Columns.

Usually, when we use Text to Columns wizard, we choose a delimiter, but this time we don’t have any. What we have is a consistent data. That’s why we choose Fixed Width.

Set delimiters, to separate year, month and day.

Click Next.

You can set data format to each column and choose a destination, or leave it as it is. Click Finish to split the data.

Now the data is divided into three columns and the 0 that was at the beginning of a day and month is removed.