Convert YYYYMMDD to DD/MM/YYYY with Excel

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

Table of Contents

Convert with formulas

With this technique, you must extract each part of a date with the LEFT, MID and RIGHT functions.

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

=LEFT(A1,4) => 2018

=RIGHT(A1,2) => 25

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

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)

But you can convert any date format to your local date format with a simple tool.

1. Select your date(s) to convert

2. Go to the menu Date > Text to Columns

3. Skip the 2 first steps (they are not useful here)

4. In the 3rd step, you select the option Date and the option YMD in the dropdown list

5. Press Finish and that's it 😃

Well, it depends of what you want to do.

Of course, the technique with the tool "Text to Columns" is easier than 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.