Convert YYYYMMDD to DD/MM/YYYY

Reading Time: 3 minutes

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

• Formulae
• Excel tool

Convert YYYYMMDD date with formulas

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

So if you have a YYYYMMDD 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 YYYYMMDD 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 a 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 to the technique is the 3rd step. With the dropdown list, you can 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 on 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 updated often, creating formulas to perform the conversion is a good solution to avoid redoing the 5 steps.