Convert YYYYMMDD to MM/DD/YYYY in Excel

Working with dates in Excel can sometimes be tricky, especially when you have them in the yyyymmdd format.

Fortunately, there are simple ways to convert these dates into a more readable format, such as mm/dd/yyyy, using built-in Excel functions and functionalities.

You may often encounter dates in the yyyymmdd format when dealing with data exports from different systems or databases.

In this article, I will show you how to convert dates in the format yyyymmdd to mm/dd/yyyy using three easy methods.

Click here to download the example file and follow along

Method 1 – Using Text-to-Columns to Convert yyyymmdd to mm/dd/yyyy in Excel

The Text-to-Column method is the best way to convert yyyymmdd to mm/dd/yyyy in the same column.

There is no need to use a formula or move the dates to another column.

The table below displays the number of items sold on different dates. Column A displays the dates, whereas Column B displays the quantity sold.

However, the dates in column A are displayed in the format yyyymmdd.

dataset with date in mmmmddyy format

In the above dataset, I want to modify the format of the date to “mm/dd/yyyy” format.

You can convert yyyymmdd to mm/dd/yyyy by following the steps below.

  1. Select all the dates that you want to convert. In this example, you have to select cells A2 to A5.
Select all the dates for which you want to change the format
  1. Click the Text-to-Column option in the “Data” tab.
Click the Text to columns option
  1. Select “Delimited” as the original data type from the “Convert Text to Columns Wizard”. Then, click the “Next” button to go to the next step.
select the delimited option and then click on next
  1. Click the “Next” button in “Convert Text to Columns Wizard – Step 2 of 3”.
click on the next button in the convert text to columns wizard step two of three
  1. In the column data format, choose “Date.” Choose “YMD” from the drop-down list next to the “Date” option. We chose YMD because year (Y) occurs first in column A, followed by the month (M), and finally day (D). Then, click the “Finish” button.
select the date option and specify the formatting as YMD

The data in “column A” is now converted to dates. But, this is not in the format that we need.

Dates not in the format we want
  1. Open the Format Cells dialog box by holding the Control key and pressing the ‘1’ key.
  2. In the Format Cells dialog box that opens, select the Custom option in the Category.
click on the custom category option in the format sales dialog box
  1. Then, enter “mm/dd/yyyy” in the type box and click the “OK” button.
Enter the mm/dd/yyyy format in the type field

The dates in Column A will then be converted to “mm/dd/yyyy” format.

Dates changed from yyyymmdd format to mm/dd/yyyy
Also read: How to Convert Date to Month and Year in Excel

Method 2 – Using TEXT function to Convert yyyymmdd to mm/dd/yyyy in Excel

In Excel, we can change yyyymmdd to mm/dd/yyyy using a number of formulas.

The Excel TEXT function is, in my opinion, the most straightforward function to convert a date.

There are several dates in the yyyymmdd format in column A of the table below. Take these dates and enter them into column B in the mm/dd/yyyy format.

Dates in the yyyymmdd format

You can use the TEXT function to convert yyyymmdd to mm/dd/yyyy. While using the TEXT function, you have to go through 2 stages.

  1. Apply the below TEXT formula.
=TEXT(A2,"0000-00-00")+0
Enter the TEXT function to convert date in yyyymmdd format to date serial number

The syntax for the TEXT function is TEXT(value, format_text).

  • value is the number to be converted. In this example, we’ve chosen cell A2 as the number to be converted.
  • The format_text argument is where we specify the format in which we want to show the date. Enter “0000-00-00” in the Format text argument to separate the year, month, and date from the date given in yyyymmdd format. Be sure to enclose it in quotations.

Then, the date is returned as “2023-01-25” by =TEXT(A2,”0000-00-00″). This date, however, is in text format. Because of this, we are not able to apply the date format.

To convert it to a date, we need to do some mathematical calculations without affecting the original date.

So, we can either add a zero (0) to the output or multiply it by one (1).

In this case, I like to add a zero at the end.

Then, =TEXT(A2,“0000-00-00”)+0 is returning 44951. This is the serial number corresponding to that date.

With the above function, we converted the values in column A to dates.

  1. The next step is to format our dates in column B using the “mm/dd/yyyy” format. To do so, we simply follow the steps outlined below.
  • Select dates in column B.
Select the date serial numbers
  • Press “Control + 1” to open the “Format cells” dialog box and go to the Custom number category.
open the format cells dialog box and select the custom option
  • Then, type “mm/dd/yyyy” in the Number Type box.
specify the date in the MMDDYYYY format

Now, all the dates are converted to mm/dd/yyyy date format. You can use these as dates for further calculations.

date serial number has been formatted as a date in MMDDYYYY format

You can use the below TEXT formulas and directly show the dates in mm/dd/yyyy format.

=TEXT(TEXT(A2,"0000-00-00"),"mm/dd/yyyy")
one single formula to convert date in YYYY MMDD into MMDDYYYY format

This formula, however, is not advised because we cannot perform any additional computations with dates.

The reason for this is that the values in column B are now saved as text rather than dates.

You will get a #VALUE! error if you try to do any calculations, such as adding 1 to the date.

Also read: How to Convert Date to Serial Number in Excel?

Method 3 – Using DATE function to Convert yyyymmdd to mm/dd/yyyy in Excel

The DATE function can also be used to directly convert yyyymmdd to a date.

With the previous method, we added 0 to the date in the text format to convert it to a date. This operation is not needed in this method.

Column A of the table below provides some dates in the yyyymmdd format. These dates should be entered into column B in the mm/dd/yyyy format.

Dates in the yyyymmdd format

To change yyyymmdd to mm/dd/yyyy, we can use the DATE function along with the RIGHT, LEFT, and MID functions. While using this method, there are two steps.

  1. Apply the below DATE formula.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Enter the DATE formula to extract YEAR MONTH, and DAY value and then create the date

Note: The result you see i column B may look different in your system based on your systems date and time settings

The syntax of the DATE function is DATE(year,month,date)

The year is the first argument in the DATE function.

We know that the year is represented by the first four digits of cell A2. We can use the LEFT function to get those first four digits. The syntax for the LEFT function is LEFT(text, [num_chars]).

We can choose cell A2 for the text because we wish to extract digits from that cell. The next argument specifies the number of characters to be extracted. Because we need the first four numbers, we provide 4 for the second argument.

The DATE function’s second argument is the month. The month is in the middle of the number.

Thus, we can use the MID function to get the month from cell A2. The MID function’s syntax is MID(text,start num,num_chars). We choose cell A2 as the text for this function as well. After that, we need to define the location of the first digit we wish to extract. The month begins with the fifth character of the cell A2 content.

Hence, for the second argument, we can enter 5. Next, we have to specify how many characters we wish to extract.

The month is typed in cell A2 with two digits (mm). Hence, for the final argument, we need to enter 2.

The DATE function’s final argument is the day. The day is represented by the last two digits in cell A2’s number.

To get the last two digits, we can use the RIGHT function. The syntax of the RIGHT function is RIGHT(text, [num_chars]).

Because we want the last two digits of Cell A2, we have to choose Cell A2 as the first argument. The next argument is the number of characters to be extracted. So we have to enter 2 because we want 2 digits from the end.

Then, we get the numbers that are in yyyymmdd format as dates.

  1. The next step is to apply the “mm/dd/yyyy” date format to our dates in column B. To do that we have to follow the steps below.
  • Select dates in column B.
select the dates
  • Press “Control + 1” to open the “Format cells” dialog box and go to the Custom number category.
select the custom option in the format cells dialog box
  • Then, type “mm/dd/yyyy” in the Number Type box.
specify the date format as MMDDYYYY

All dates are now converted to mm/dd/yyyy format. You can use these as dates in further calculations.

Dates have been converted to MMTDDYYYY format
Also read: How to Convert Date to Day of Week in Excel?

Click here to download the example file and follow along

Best Practices for Handling Dates in Excel

When working with dates in Excel, it’s essential to use proper formats to avoid errors and confusion. Here are some best practices for handling dates:

  1. Stick to a consistent date format like mm/dd/yyyy throughout your workbook. This helps prevent misunderstandings and makes it easier to read and understand the data.
  2. Convert dates stored as text using Excel functions like DATEVALUE. This ensures that Excel recognizes and treats them as true dates, enabling you to perform calculations or apply formatting.
  3. In case you have dates that require manual intervention, consider using Excel’s Text to Columns feature to parse and reformat them.
  4. Avoid hard-coding date values in formulas or conditional formatting rules. Instead, reference date cells or use named ranges to make your workbook more robust and maintainable.

Remembering these best practices while working with dates in Excel will make your tasks smoother and more efficient. Happy date formatting!

In this article, we have learned two formula solutions and one non-formula solution for converting yyyymmdd to mm/dd/yyyy in Excel.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment