How to Fix Excel Treating 31/12/2022 as Text Instead of a Date in WPS Office
Question details
The user needs to understand why 31/12/2022 is treated as text and how to convert it to a recognized date in Excel or WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user opens or enters day/month/year dates such as 31/12/2022 while the spreadsheet application or operating system expects month/day/year.
- Observed behavior
- The cell remains text, date functions return errors or incorrect results, and sorting or grouping by date does not work as expected.
Check the current regional date format in your operating system and confirm whether the problematic cell is formatted as Text or General. This determines whether you can change the cell format directly or need to convert the text value first.
Convert 31/12/2022 Text Dates with Text to Columns
Use this formula-free method when you have many dd/mm/yyyy text dates in one column and need to turn them into real dates quickly.
Excel and WPS Office Spreadsheets leave 31/12/2022 as text when the active locale expects m/d/yyyy because 31 is not a valid month. Text to Columns forces the application to re-interpret the existing values as dates using a DMY order.
Open the workbook in WPS Office Spreadsheets and select the column that contains 31/12/2022 or similar text dates.
Go to the Data tab on the ribbon and click Text to Columns.
In the wizard, click Next, click Next again, then select Date under Column data format and choose DMY from the dropdown.
Click Finish and confirm the cells are right-aligned. Press Ctrl+1 and set the Number format to Date if you want a specific display format.
Set a Matching Regional Date Format Before Entering Dates
Use this method to prevent future dd/mm/yyyy entries from being treated as text by aligning the operating system and spreadsheet locale.
Rebuild the Date with a DATE Formula
Use this formula when the source text must stay as-is and you need a calculated date column for reporting or further analysis.
Convert Text Dates to Real Dates in WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets and use Data > Text to Columns with the DMY date order, or use a DATE formula, to convert 31/12/2022 text entries into real dates. WPS Office handles Excel date workflows without recreating the file.
- 1. Open the Excel workbook: Launch WPS Office, click Menu > Open, and select the Excel file containing 31/12/2022 text dates.
- 2. Select the affected date column: Click the column letter to select the entire column, or drag to select only the cells you want to convert.
- 3. Run Text to Columns: Go to the Data tab and click Text to Columns. Click Next, click Next again, then select Date and choose DMY.
- 4. Finish and verify: Click Finish. Confirm the cells are right-aligned, then press Ctrl+1 and set a Date number format if necessary.
Frequently Asked Questions
Why does Excel treat 31/12/2022 as text when my system is set to the United States?
Because the United States regional format expects m/d/yyyy. Since 31 is not a valid month, Excel and WPS Office Spreadsheets leave the entry as text instead of converting it to a date.
Can I convert dd/mm/yyyy text dates without a formula?
Yes. Select the column, go to Data > Text to Columns, click Next twice, choose Date, select DMY, and click Finish.
What formula converts 31/12/2022 from text to a real date?
Use =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) in a helper column. If your regional list separator is a semicolon, replace commas with semicolons.
Will WPS Office Spreadsheets open Excel files that have this text date issue?
Yes. WPS Office Spreadsheets opens Excel workbooks and includes Data > Text to Columns, Date number formats, and date functions, so you can fix the issue without Microsoft Excel.
How do I prevent future dd/mm/yyyy entries from being treated as text?
Set your operating system regional format to a locale that uses dd/mm/yyyy, reopen the spreadsheet, and apply a Date number format to the cells before entering dates.
