Data Import & Export

Convert DMY Text Dates to YMD Format in Excel

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

Convert text dates from day/month/year into real spreadsheet dates and display them in year/month/day order.

Convert DMY Text Dates to YMD Format in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the dates

In Excel, select the cells or column containing the text dates.

2
Open Text to Columns

Go to Data > Text to Columns, select Delimited, and click Next.

3
Keep each date together

Clear any selected delimiters, check that each date appears in one column in the preview, and click Next.

4
Convert as DMY

Under Column data format, select Date, choose DMY from the list, and click Finish.

5
Apply the YMD display

With the converted cells selected, open Format Cells, choose Number > Custom, enter yyyy/mm/dd, and click OK.

Convert DMY text dates with Excel Text to Columns
Format versus value: The custom format changes how real dates are displayed; it does not change the underlying date. Check any cells that remain as text for invalid or inconsistent date values.
Convert dates in WPS Spreadsheets

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. 1. Open the spreadsheet: Open your file in WPS Spreadsheets and locate the column containing DD/MM/YYYY text dates.
  2. 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. 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. 4. Format the results: Select the converted cells, open Format Cells, choose a custom number format, and enter yyyy/mm/dd.
Convert a column of text dates without editing each cellKeep the original text dates while checking the resultsWork with Microsoft Excel XLSX and XLS filesSort and calculate using the converted date values
microsoft office alternative - wps office

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.