**This article will explain how to convert date YYYYMMDD to DD/MM/YYYY. You can use one of two techniques:**

## Convert with formulas

With this technique, you must extract each part of a date with the text functions.

So if you have a YYYYYMMDD format to transform, here are the steps to follow.

### Step 1: Extract the year

=LEFT(A1,4) => 2018

### Step 2: Extract the day

=RIGHT(A1,2) => 25

### Step 3: Extract the month

This step is a little bit more difficult because you must extract 2 characters in the middle of your string. In that case, you use the function MID.

=MID(A1,5,2) => 12

You extract 2 characters from the 5th position

### Step 4: Convert each part as a date

Now we are going to gather each part of the date to return a "real" date. To do this we use the DATE function.

=DATE(B1,D1,C1)

## Convert without formula

But you can convert YYYYMMDD date, or any other date format, to your local date format with the tool **Text to Columns**. Normally this tool is used to split text with delimiter.

1. Select your date(s) to convert

2. Go to the menu **Data > Text to Columns**

3. **Skip the 2 first steps** because there is no need to split your column 😉

4. **The key is the 3rd step**. With the dropdown list, you can selected the type of date format that you have in your column.

So, in our example, we are going to select **YMD **because our dates are written **YYYYMMDD**.

5. Press **Finish **and that's it 😃

## Formula or not ?

Well, it depends of what you want to do. Of course, the technique with the tool "**Text to Columns**" is easier than the formulas.

But if your list will be update often, creating formulas to perform the conversion is a good solution to avoid to redo the 5 steps.

## 10 comments

I found the thrice conversions from number to text to number (since parameters to DATE() are numbers) to be quite a load when dealing with big datasets.

So I spent some time to try to optimise it; and I ended (for the moment) with

= DATE( A1/10000, MOD(A1/100, 100), MOD(A1,100) )

(I added spaces just for presentation.)

It relies on the fact that Excel rounds to integer the arguments when invoking DATE or MOD; it somewhat assumes that the number in A1 is between 19000000 and 20something. It even works OK when the cell in A1 actually contains a text value which looks like a yyyymmdd date (like your proposals do), which could prove useful.

Thank you very much for this. It has saved me a lot of time. Much appreciated. Lester

Thank you so much for the solution. very quick and easy

i have a program to write out the function =date(2018,1,30) when the sheet is created. When I open the sheet, it is displayed as 43130, the serial number. How do I get it to display as 1/30/2018 without having to open the sheet, double click to edit the cell then enter to display the date format?

It's because your cell as the format Number "General". Have a look at this article to apply the format you want

=DATEVALUE(RIGHT([date],2)&"/"&MID([date],5,2)&"/"&LEFT([date],4))... does it all in one cell...

Right

Very useful. Thank you.

Excellent tool

Your message. hi was trying to enter date and is not going what should I do please