Convert DMY Text Dates to YMD Format in Excel
Question details
Convert text dates from day/month/year into real spreadsheet dates and display them in year/month/day order.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains thousands of dates stored as text, and the user wants to convert and format them in bulk.
- Observed behavior
- The text values need to become real dates so they can be sorted and used in calculations, while displaying in YMD order.
Check that the selected cells contain valid dates in day/month/year order and that they use a consistent format. Text to Columns replaces the selected values, so confirm that the entire selection is the date column you want to convert.
Convert DMY text dates with Excel Text to Columns
Use Text to Columns to convert an entire column of DMY text dates, then apply a YMD display format.
In Excel, select the cells or column containing the text dates.
Go to Data > Text to Columns, select Delimited, and click Next.
Clear any selected delimiters, check that each date appears in one column in the preview, and click Next.
Under Column data format, select Date, choose DMY from the list, and click Finish.
With the converted cells selected, open Format Cells, choose Number > Custom, enter yyyy/mm/dd, and click OK.

Convert DMY text dates to YMD in WPS Spreadsheets
Use a formula in WPS Spreadsheets to turn consistently formatted DD/MM/YYYY text into real dates, then apply a YMD display format.
- 1. Open the spreadsheet: Open your file in WPS Spreadsheets and locate the column containing DD/MM/YYYY text dates.
- 2. Enter the conversion formula: If the first text date is in A2, select an empty cell such as B2 and enter =DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))). This formula requires valid dates with two-digit days and months and a four-digit year.
- 3. Fill down the column: Select B2 and drag or double-click its fill handle to copy the formula down alongside the date entries.
- 4. Format the results: Select the converted cells, open Format Cells, choose a custom number format, and enter yyyy/mm/dd.

Frequently Asked Questions
Why does changing the number format not convert my text dates?
A number format changes the display of a real date but does not convert text. In Excel, use Data > Text to Columns and select Date > DMY before applying the custom format yyyy/mm/dd.
Will converting the dates change the day and month?
Selecting DMY in the Text to Columns wizard tells Excel to interpret the first number as the day and the second as the month. Applying yyyy/mm/dd then changes the display order, not the underlying date.
How can I tell whether the converted dates are real dates?
Select a converted cell, open Format Cells, and temporarily choose General. A real Excel date displays as a serial number; you can then restore the yyyy/mm/dd custom format.
What if some dates do not convert?
Check the affected cells for invalid dates, extra characters, or a different day/month/year pattern. Correct those entries and run Data > Text to Columns again on the affected cells.




