Converting a date from the format YYYYMMDD to DD/MM/YYYY is simple in Excel. You can use either a formula or the Text to Column tool. Here’s how to do it.
#1 Extract each Date parts with 3 different function
Suppose the date is in cell A1 (e.g., 20241028 for 28th October 2024). The following formula works for any Excel version
=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2))
Explanation of the Formula:
LEFT(A1,4): Extracts the first 4 characters from A1, which represent the year.MID(A1,5,2): Extracts 2 characters from the middle, starting at the 5th position, for the month.RIGHT(A1,2): Extracts the last 2 characters for the day.- The DATE function then combines these parts into a proper date format.
#2 Extract each Date parts by following a pattern
If you use Excel 365, you can use the trick with the MID function with an array as an argument. The array represents the pattern of your date and also how to reorder the output 😮👍
#3 Using Text to Column Tool
However, you can convert a YYYYMMDD date or any other date format to your local date format with the tool Text to Columns. This tool is normally used to split text with a delimiter, but here, we will trick it 😉
- Select the column with the YYYYMMDD dates.
- Go to Data > Text to Columns.
- Skip the 2 first steps because there is no need to split your column
- 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 will choose YMD because our dates are written YYYYMMDD.
- Press Finish and that's it 😃 But be aware that this technique replaces your source.
Benefits of These Techniques
- Formula Method: Useful when your dates could be updated. With formulas, the result will be automatically refreshed.
- Text to Column Tool: Ideal for one-time conversions or when working interactively. It’s easy to follow without complex formulas.
Both methods ensure your dates are readable and usable, especially when working with international date formats or preparing data for reports.
