While dealing with dates its common to come across different date formats. Thus you need to know how to convert across different date formats. This will help in achieving consistency in the spreadsheet that you are working on. The most popular date format that people usually convert is from YYYYMMDD to DD/MM/YY.
In this tutorial, I will show you several ways that you can easily achieve this easily.
Using Formula
To convert a date in the format YYYYMMDD to DD/MM/YYYY in Excel, you can follow these steps:
1. Insert a New Column: In your Excel spreadsheet, insert a new column where you want the converted dates to appear. This column can be adjacent to the column containing the original YYYYMMDD dates.
2. Assuming the original date is in cell A1, enter the following formula in the new column (let’s say it’s column B1):
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
This formula uses the DATE function to create a date value using the year, month, and day extracted from the original date.
3. Copy the Formula Down: After entering the formula in cell B1, drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to all the cells in the new column where you want to convert dates.
4. Custom Format the Cells: Select the cells in the new column (column B) that contain the converted dates. Then, right-click and choose “Format Cells.”
5. In the “Format Cells” dialog, go to the “Number” tab and select “Date” from the list on the left. Choose the desired date format, such as DD/MM/YYYY, from the options on the right.
6. Click “OK” to apply the date format.
Your YYYYMMDD dates should now be converted to the DD/MM/YYYY format in the new column (column B).
Please ensure that the original dates are in a format that Excel recognizes as text. If not, you might need to use text functions to extract the year, month, and day components before using the DATE function.
Practice Template
Using Text Function
Another method to convert the date format from YYYYMMDD to DD/MM/YYYY in Excel is to use a combination of text functions. Here are the steps for this method:
1. Insert a New Column: As with the previous method, insert a new column where you want the converted dates to appear. This column can be adjacent to the column containing the original YYYYMMDD dates.
2. Assuming the original date is in cell A1, enter the following formula in the new column (let’s say it’s column B1):
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),”dd/mm/yyyy”)
This formula uses the `TEXT` function to format the date created by the `DATE` function into the desired DD/MM/YYYY format.
3. Copy the Formula Down: After entering the formula in cell B1, drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to all the cells in the new column where you want to convert dates.
This method combines the `DATE` function to create a date value and the `TEXT` function to format it in the desired date format. It provides an alternative way to achieve the conversion from YYYYMMDD to DD/MM/YYYY in Excel.
How to change YYYY-MM-DD to DD-MM-YYYY
To change from YYYY-MM-DD to DD-MM-YYY you need to use the Text function. Here are the steps:
1. First type the following formula in the cell adjacent to the one with a YYYY-MM-DD date.
=TEXT(C2,”DD-MM-YYYY”)
2. Press enter
3. Double-click on the fill handle to apply the formula to the rest of the cells
That is you have now converted YYYY-MM-DD to DD-MM-YYYY.
If you want to have the month short form eg Jan, Feb, etc. you can use triple MMM in the formula as follows.
=TEXT(C2,”DD-MMM-YYY)
How to Convert Convert YYYYMMDD to MM/DD/YYYY in Excel
You can use the `DATE` function to convert the date format from YYYYMMDD to MM/DD/YYYY in Excel. Here are the steps:
1. Insert a New Column: As usual, insert a new column where you want the converted dates to appear. This column can be adjacent to the column containing the original YYYYMMDD dates.
2. Assuming the original date is in cell A1, enter the following formula in the new column (let’s say it’s column B1):
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
This formula uses the `DATE` function to create a date value using the year, month, and day extracted from the original date.
3. Custom Format the Cells: After entering the formula, you may notice that Excel displays the date in its default format. To change the format to MM/DD/YYYY, you can do the following:
– Select the cells in the new column (column B) that contain the converted dates.
– Right-click and choose “Format Cells.”
In the “Format Cells” dialog, go to the “Number” tab and select “Date” from the list on the left. Choose the desired date format, such as MM/DD/YYYY, from the options on the right.
4. Click “OK” to apply the date format.
Your YYYYMMDD dates should now be converted to the MM/DD/YYYY format in the new column (column B) using the `DATE` function.