# Convert YYYYMMDD to DD/MM/YYYY

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.

1. Select your date(s) to convert

2. Go to the menu Date > Text to Columns

3. Skip the 2 first steps because you don't want to split anything in your column.

4. In the 3rd step is the most important. With the dropdown list, you select 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.

## Related posts

• Linda on 23/01/2020 at 22:41

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?

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

• Stephen on 24/12/2019 at 12:10

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

1. Right

• Kenif C on 30/10/2019 at 02:31

Very useful. Thank you.

• Rajesh Kadia on 24/09/2019 at 06:09

Excellent tool