Convert YYYYMMDD to DD/MM/YYYY with Excel

Last Updated on 04/06/2024
Reading time: 3 minutes

How to convert the date YYYYMMDD to DD/MM/YYYY. You can use one of two techniques:

  1. With Formula

    A combination of the functions DATE, LEFT, MID and RIGHT convert your date

  2. With an internal Excel tool

    The tool Text to Column do the job for you in no time

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.

#1: Extract the year

#2: Extract the day

RIGHT function to extract the day

#3: Extract the month

This step is slightly more difficult because you must extract 2 characters in the middle of your string. In that case, you use the function MID. You extract 2 characters from the 5th position

MID function extract the month

#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 function creates a real date with the 3 arguments day, month and year

Convert your date YYYYMMDD with a single formula

A new trick with the MID function and an array, you can extract the date with a single formula.

Splitting date to return dd mm yyyy

Convert YYYYMMDD with a tool (and no 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 dates to convert
Selection of the column with the dates
  1. Go to the menu Data > Text to Columns
Menu Text to Columns
  1. Skip the 2 first steps because there is no need to split your column 😉
  2. 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.
Option to select the date format to return

Press Finish and that's it 😃

Date convert in standard format without formula

Formula or not?

  • Purpose Matters: It really depends on your specific needs.
  • Ease of Use: The "Text to Columns" tool is simpler than using formulas.
  • Frequent Updates: If your list updates often, formulas are a smart choice.

19 Comments

  1. ROBIN
    11/07/2023 @ 14:33

    THANK YOU SO MUCH SIR! THE FORMULA WAS VERY USEFUL.

    Reply

  2. Gajanan Funde
    28/07/2022 @ 13:38

    Very Useful. Thanks a lot.

    Reply

  3. CA. Shivanand
    31/05/2022 @ 13:25

    Very useful made my work more easier. Thank You

    Reply

    • Frédéric LE GUEN
      31/05/2022 @ 15:25

      You're welcome

      Reply

      • Cassie Paulus
        02/11/2022 @ 21:55

        You’re the best! I have been looking for a solution to this for a while now. Thank you for sharing!

      • Frédéric LE GUEN
        08/11/2022 @ 10:54

        You're welcome

  4. Rick Rothstein
    19/11/2021 @ 03:53

    I think the simplest formula to convert those values would be this one...

    =0+TEXT(A1,"0000-00-00")

    Note: This returns the date serial number so you will have to apply a cell format to make it display the way you want.

    Reply

    • Daniel
      16/12/2022 @ 06:45

      Thank you! I tried Frederic's approach but failed to get the intended results. I checked whether if my steps had gone wrong, or were the cells' format wrong etc, i didn't manage to troubleshoot. Rick, your formula worked instantaneously. Thank you Rick and Frederic for your kind contribution to everyone who needed help! Appreciated

      Reply

  5. Robert
    26/10/2021 @ 18:43

    If the date in format yyyymmdd is the result of a formula (e.g. extracting it from a longer field using MID(...)), the text to column method does not work, since excel tries to split the formula itself and not the value. Of course, trying to split for example
    = mid(a1;10;8)
    using dmy format does not work.

    Reply

  6. Antoine
    09/09/2021 @ 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

  7. Lester Cantem
    25/08/2021 @ 02:25

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

    Reply

  8. Vrishali
    21/05/2021 @ 19:23

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

    Reply

  9. Linda
    23/01/2020 @ 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 @ 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

  10. Stephen
    24/12/2019 @ 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 @ 06:29

      Right

      Reply

  11. Kenif C
    30/10/2019 @ 02:31

    Very useful. Thank you.

    Reply

  12. Rajesh Kadia
    24/09/2019 @ 06:09

    Excellent tool

    Reply

  13. badejo saheed adetunbosun
    27/06/2019 @ 16:13

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Convert YYYYMMDD to DD/MM/YYYY with Excel

Reading time: 3 minutes
Last Updated on 04/06/2024

How to convert the date YYYYMMDD to DD/MM/YYYY. You can use one of two techniques:

  1. With Formula

    A combination of the functions DATE, LEFT, MID and RIGHT convert your date

  2. With an internal Excel tool

    The tool Text to Column do the job for you in no time

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.

#1: Extract the year

#2: Extract the day

RIGHT function to extract the day

#3: Extract the month

This step is slightly more difficult because you must extract 2 characters in the middle of your string. In that case, you use the function MID. You extract 2 characters from the 5th position

MID function extract the month

#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 function creates a real date with the 3 arguments day, month and year

Convert your date YYYYMMDD with a single formula

A new trick with the MID function and an array, you can extract the date with a single formula.

Splitting date to return dd mm yyyy

Convert YYYYMMDD with a tool (and no 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 dates to convert
Selection of the column with the dates
  1. Go to the menu Data > Text to Columns
Menu Text to Columns
  1. Skip the 2 first steps because there is no need to split your column 😉
  2. 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.
Option to select the date format to return

Press Finish and that's it 😃

Date convert in standard format without formula

Formula or not?

  • Purpose Matters: It really depends on your specific needs.
  • Ease of Use: The "Text to Columns" tool is simpler than using formulas.
  • Frequent Updates: If your list updates often, formulas are a smart choice.

19 Comments

  1. ROBIN
    11/07/2023 @ 14:33

    THANK YOU SO MUCH SIR! THE FORMULA WAS VERY USEFUL.

    Reply

  2. Gajanan Funde
    28/07/2022 @ 13:38

    Very Useful. Thanks a lot.

    Reply

  3. CA. Shivanand
    31/05/2022 @ 13:25

    Very useful made my work more easier. Thank You

    Reply

    • Frédéric LE GUEN
      31/05/2022 @ 15:25

      You're welcome

      Reply

      • Cassie Paulus
        02/11/2022 @ 21:55

        You’re the best! I have been looking for a solution to this for a while now. Thank you for sharing!

      • Frédéric LE GUEN
        08/11/2022 @ 10:54

        You're welcome

  4. Rick Rothstein
    19/11/2021 @ 03:53

    I think the simplest formula to convert those values would be this one...

    =0+TEXT(A1,"0000-00-00")

    Note: This returns the date serial number so you will have to apply a cell format to make it display the way you want.

    Reply

    • Daniel
      16/12/2022 @ 06:45

      Thank you! I tried Frederic's approach but failed to get the intended results. I checked whether if my steps had gone wrong, or were the cells' format wrong etc, i didn't manage to troubleshoot. Rick, your formula worked instantaneously. Thank you Rick and Frederic for your kind contribution to everyone who needed help! Appreciated

      Reply

  5. Robert
    26/10/2021 @ 18:43

    If the date in format yyyymmdd is the result of a formula (e.g. extracting it from a longer field using MID(...)), the text to column method does not work, since excel tries to split the formula itself and not the value. Of course, trying to split for example
    = mid(a1;10;8)
    using dmy format does not work.

    Reply

  6. Antoine
    09/09/2021 @ 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

  7. Lester Cantem
    25/08/2021 @ 02:25

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

    Reply

  8. Vrishali
    21/05/2021 @ 19:23

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

    Reply

  9. Linda
    23/01/2020 @ 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 @ 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

  10. Stephen
    24/12/2019 @ 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 @ 06:29

      Right

      Reply

  11. Kenif C
    30/10/2019 @ 02:31

    Very useful. Thank you.

    Reply

  12. Rajesh Kadia
    24/09/2019 @ 06:09

    Excellent tool

    Reply

  13. badejo saheed adetunbosun
    27/06/2019 @ 16:13

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *