Convert DD.MM.YYYY Text Dates to US Date Format in WPS Office Spreadsheets
Question details
The user needs to convert a text value such as 13.08.2023 into a valid spreadsheet date so it can be displayed in the US MM/DD/YYYY format.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A column contains dates entered as DD.MM.YYYY text and the user wants them formatted as MM/DD/YYYY for US-style reporting or analysis.
- Observed behavior
- The values are stored as text, so changing the display format alone does not convert them to 08/13/2023.
Open the workbook in WPS Office Spreadsheets and identify the column containing the DD.MM.YYYY text values. Make sure the sheet is not protected so you can use the Data tab commands.
Convert DD.MM.YYYY Text Dates with Text to Columns in WPS Spreadsheets
Use this method when the dates are in a single column and you want to convert them in place without creating formulas.
Text to Columns interprets the existing text according to the date order you specify. In this case, DMY tells WPS Spreadsheets that the first part is the day, the second part is the month, and the third part is the year.
Open the workbook in WPS Office Spreadsheets and click the column letter that contains the DD.MM.YYYY text values.
Go to the Data tab and click Text to Columns in the Data Tools group.
In the Convert Text to Columns Wizard, select Delimited, click Next, click Next again, then choose Date and select DMY from the drop-down list.
Click Finish. With the converted cells still selected, press Ctrl+1, choose Date, and select MM/DD/YYYY, or type MM/DD/YYYY under Custom.
Use the DATE Function to Convert DD.MM.YYYY Text in a Separate Cell
Choose this formula method when you want to keep the original text date column unchanged.
Convert DD.MM.YYYY Text Dates to MM/DD/YYYY with WPS Office
WPS Office Spreadsheets has a built-in Text to Columns wizard that turns DD.MM.YYYY text into real date values, which you can then format as MM/DD/YYYY for US date reporting.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website, install it, and open your workbook in WPS Spreadsheets.
- 2. Select the DD.MM.YYYY column: Click the column letter to select the text dates you want to convert.
- 3. Run Data to Text to Columns with DMY: On the Data tab, click Text to Columns, choose Delimited, click Next twice, select Date, choose DMY, and click Finish.
- 4. Format as MM/DD/YYYY: Press Ctrl+1, choose Date, select MM/DD/YYYY, and click OK.
Frequently Asked Questions
Why does my date still not change when I apply MM/DD/YYYY formatting?
The cell is likely still stored as text. Convert it first with Data > Text to Columns using the DMY date order, then apply the MM/DD/YYYY format.
Can I convert DD.MM.YYYY values with a formula instead of Text to Columns?
Yes. If A2 contains the text date, use =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) and then format the result cell as MM/DD/YYYY.
Does Text to Columns work if my dates have leading zeros, such as 05.08.2023?
Yes. The DMY conversion handles two-digit day and month components correctly, and after conversion you can format the cells as MM/DD/YYYY.
Will converting to a real date overwrite my original text values?
Text to Columns overwrites the selected cells. Use the formula method in a separate cell if you need to keep the original DD.MM.YYYY column unchanged.
Is this text date conversion compatible with Microsoft Excel files?
Yes. WPS Office Spreadsheets can open and save .xlsx files, and the converted date values remain compatible with Microsoft Excel.
