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.
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.
- Select all the dates that you want to convert. In this example, you have to select cells A2 to A5.
- Click the Text-to-Column option in the “Data” tab.
- 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.
- Click the “Next” button in “Convert Text to Columns Wizard – Step 2 of 3”.
- 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.
The data in “column A” is now converted to dates. But, this is not in the format that we need.
- Open the Format Cells dialog box by holding the Control key and pressing the ‘1’ key.
- In the Format Cells dialog box that opens, select the Custom option in the Category.
- Then, enter “mm/dd/yyyy” in the type box and click the “OK” button.
The dates in Column A will then be converted to “mm/dd/yyyy” format.
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.
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.
- Apply the below TEXT formula.
=TEXT(A2,"0000-00-00")+0
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.
- 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.
- Press “Control + 1” to open the “Format cells” dialog box and go to the Custom number category.
- Then, type “mm/dd/yyyy” in the Number Type box.
Now, all the dates are converted to mm/dd/yyyy date format. You can use these as dates for further calculations.
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")
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.
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.
- Apply the below DATE formula.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
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.
- 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.
- Press “Control + 1” to open the “Format cells” dialog box and go to the Custom number category.
- Then, type “mm/dd/yyyy” in the Number Type box.
All dates are now converted to mm/dd/yyyy format. You can use these as dates in further calculations.
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:
- 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.
- 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.
- In case you have dates that require manual intervention, consider using Excel’s Text to Columns feature to parse and reformat them.
- 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:
- How to Convert Text to Date in Excel?
- How to Remove Year from Date in Excel?
- How to Separate Date and Time in Excel?
- Add Days To Date In Excel
- How To Combine Date and Time in Excel?
- Calculate Days Between Two Dates in Excel (Workdays/Weekends)
- How to Calculate the Number of Months Between Two Dates in Excel?
- How to Convert Month Name to Number in Excel?
