How to Convert Text Like 20-Sep-2023 into an Excel Date in WPS Office Spreadsheets
Question details
The user needs to convert a text value such as 20-Sep-2023 into a real Excel date value so it can be used with TODAY() and other date formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A date extracted from an external system appears as 20-Sep-2023, but the spreadsheet treats it as text instead of a date.
- Observed behavior
- WPS Spreadsheets stores the value as text, so date comparisons and formulas such as TODAY() do not work correctly.
Check that the text date uses a month abbreviation WPS Spreadsheets recognizes, such as Sep. If you need to keep the original text values, place them in a helper column before converting.
Convert a Text Date Cell with DATEVALUE in WPS Spreadsheets
Best for single cells or when you want the converted date in a separate cell while preserving the original text.
Open your workbook in WPS Office Spreadsheets and select the worksheet that contains the text date.
Click the cell where you want the real date result, type =DATEVALUE(A1) and replace A1 with the cell containing the text date, then press Enter.
Keep the result selected, go to Home > Number Format, and choose Short Date or Long Date.
Convert a Whole Column of Text Dates with Text to Columns
Best for a consistent column of dates when you want to overwrite the text values with real dates in place.
Parse a Text Date Explicitly with DATE, MONTH, LEFT, and RIGHT
Use when DATEVALUE or Text to Columns does not recognize the month abbreviation, or when you need a formula-based extraction.
Convert Text Dates to Real Excel Dates in WPS Office Spreadsheets
Use WPS Office Spreadsheets to convert text such as 20-Sep-2023 into real date values with the DATEVALUE formula or Text to Columns, then apply date formatting so comparisons with TODAY() work correctly.
- 1. Open your workbook: Open the workbook in WPS Office Spreadsheets and locate the text date such as 20-Sep-2023.
- 2. Use DATEVALUE for one cell: Click an empty cell, type =DATEVALUE(A1), replace A1 with the text date cell, and press Enter.
- 3. Apply a date format: Select the result cell, go to Home > Number Format, and choose Short Date or Long Date.
- 4. Convert a full column: Select the column, click Data > Text to Columns, choose Delimited, clear all delimiters, then select Date and DMY in the final step.
- 5. Confirm the date value: Use =ISNUMBER(A1) to verify that the cell now contains a numeric date value rather than text.
Frequently Asked Questions
Why does =TODAY()-A1 return #VALUE! when A1 contains 20-Sep-2023?
Because A1 contains text instead of a real date serial number. Convert it with =DATEVALUE(A1) or Text to Columns so date arithmetic can work.
Does DATEVALUE work for all text date formats?
DATEVALUE works when the text matches a date format recognized by WPS Spreadsheets, including recognized month abbreviations. If it fails, use Text to Columns and explicitly choose DMY, or parse the date with DATE, MONTH, LEFT, and RIGHT.
How do I convert a whole column of text dates without formulas?
Select the column, go to Data > Text to Columns, choose Delimited, clear all delimiter checkboxes, click Next, select Date, choose DMY, and click Finish.
How can I check that the conversion produced a real date?
Use =ISNUMBER(A1) on the converted cell. A real date returns TRUE because WPS Spreadsheets stores dates as numeric serial numbers.
What if my region normally uses MDY dates instead of DMY?
DATEVALUE depends on regional settings. If it reads the date incorrectly, select the column and use Data > Text to Columns, then explicitly choose Date and DMY in the final step.
