Date

Convert YYYYMMDD to DD/MM/YYYY

Reading Time: 3 minutes

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

RIGHT function to extract the day

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

MID function extract the month

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)

DATE function creates a real date with the 3 arguments day, month and year

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

Selection of the column with the dates

2. Go to the menu Data > Text to Columns

Menu Text to Columns

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

Skip the 2 first steps

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.

Option to select the date format to return

5. Press Finish and that's it 😃

Date convert in standard format without formula

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

Weekly calendar 2020 in one formula

Frédéric LE GUEN

Time Format in Excel

Frédéric LE GUEN

Title with date in Excel

Frédéric LE GUEN

10 comments

Antoine 09/09/2021 at 16:17

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.

Reply
Lester Cantem 25/08/2021 at 02:25

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

Reply
Vrishali 21/05/2021 at 19:23

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

Reply
Linda 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?

Reply
Frédéric LE GUEN 24/01/2020 at 10:10

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

Reply
Stephen 24/12/2019 at 12:10

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

Reply
Frédéric LE GUEN 03/01/2020 at 06:29

Right

Reply
Kenif C 30/10/2019 at 02:31

Very useful. Thank you.

Reply
Rajesh Kadia 24/09/2019 at 06:09

Excellent tool

Reply
badejo saheed adetunbosun 27/06/2019 at 16:13

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

Reply

Leave a Comment