WPS Spreadsheets: Convert Text That Looks Like Dates into Real Dates
Question details
The user needs to convert date-looking text into true spreadsheet dates that can be reformatted and used with functions such as YEAR.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A column contains values that look like dates, but the dates cannot be reformatted or used reliably in date formulas.
- Observed behavior
- The values are stored as text instead of date serial numbers, so date formatting and the YEAR function do not work as expected.
Check one sample cell with =ISNUMBER(A1), replacing A1 with the relevant cell. Confirm whether the source dates use month-day-year, day-month-year, or year-month-day order before converting the column.
Convert text dates with Text to Columns
Use this method when ISNUMBER returns FALSE and the entire column contains consistently formatted date text.
Formatting alone does not convert text into dates. Text to Columns reinterprets the selected values using the date order you specify and stores them as usable date values.
Click a sample cell and enter =ISNUMBER(A1) in an empty cell, replacing A1 with the sample reference. A FALSE result indicates that the date is text.
Select the full column or range containing the text dates, then open the Data tab and choose Text to Columns.
In the Text to Columns dialog, select Delimited if prompted, leave the delimiter options unchanged, and click Next until you reach the column data format step.
Select Date under Column data format, choose the matching order such as MDY, DMY, or YMD, and click Finish.
Select the converted cells and use the Home tab's number-format controls or Format Cells to apply a format such as yyyy. To extract the year, enter =YEAR(A1) in a nearby cell.
Convert individual text dates with a formula
Use a formula-based approach when only a few cells need conversion or when you want to preserve the original text column.
Convert text dates and use them in WPS Spreadsheets
WPS Spreadsheets provides Text to Columns, date formatting, and familiar formulas to turn date-looking text into real date values that work with YEAR and other spreadsheet functions.
- 1. Open the spreadsheet: Launch WPS Office, open the workbook, and select the worksheet containing the text dates.
- 2. Check whether dates are numeric: Enter =ISNUMBER(A1) in an unused cell to test a representative date value.
- 3. Open Text to Columns: Select the date column, open the Data tab, and click Text to Columns.
- 4. Set the date format: Advance to the final wizard step, choose Date, select the correct MDY, DMY, or YMD order, and click Finish.
- 5. Format and use the dates: Use the Home tab or Format Cells to apply the desired date format, then use formulas such as =YEAR(A1).
Frequently Asked Questions
How can I tell whether a date is text in WPS Spreadsheets?
Enter =ISNUMBER(A1) in an empty cell, replacing A1 with the date cell. TRUE means the value is numeric and FALSE usually means it is stored as text.
Why does changing the date format not fix the value?
Number formats only change the appearance of numeric dates. If the value is text, convert it first with Data > Text to Columns or a formula such as =DATEVALUE(A1).
Which date order should I select in Text to Columns?
Choose the order that matches the source text: MDY for month-day-year, DMY for day-month-year, or YMD for year-month-day. Selecting the wrong order can create incorrect dates.
Why does the YEAR function return an error or an unexpected result?
YEAR requires a valid numeric date. Convert the text first, confirm that =ISNUMBER(A1) returns TRUE, and then use =YEAR(A1).
Can I preserve the original text dates while converting them?
Yes. Use a nearby column with =DATEVALUE(A1), fill the formula down, and keep the original column. You can copy the results and choose Paste Special > Values when a standalone converted column is needed.
