Convert YYYYMMDD Text to a Date in WPS Spreadsheets and Fix ####### Errors
Question details
The user needs to convert imported YYYYMMDD values into valid date values in WPS Spreadsheets and resolve ####### display errors caused by narrow columns.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- You imported data that contains dates stored as YYYYMMDD text and need to sort, filter, or calculate with real date values.
- Observed behavior
- Values like 20230103 remain text instead of dates, and the cells may display ####### when the column is not wide enough.
Open the workbook in WPS Spreadsheets and identify the column that contains YYYYMMDD values. Confirm whether the values are stored as text or as numbers so you can choose the fastest conversion method.
Use Text to Columns to convert YYYYMMDD text to dates
Best for one-time imports and mixed date columns because it converts the values in place without formulas.
This is the fastest no-formula method for an imported column. It converts the text in place and keeps the original column layout.
Click the column letter at the top of the sheet to select the entire column that contains the YYYYMMDD text values.
Go to the Data tab in the WPS Spreadsheets ribbon and click Text to Columns.
In the Text to Columns wizard, select Delimited and click Next twice to reach the Column data format step.
Under Column data format, choose Date, open the format drop-down list, select YMD, and click Finish.
Select the converted range, go to Home > Number Format, choose Date, and drag the column boundary or choose Home > Format > AutoFit Column Width if ####### appears.

Use a DATE formula for repeated or calculated conversions
Use this when the source YYYYMMDD values update regularly and you want the date result to recalculate automatically.
Convert YYYYMMDD Text to Real Dates in WPS Spreadsheets
Use WPS Spreadsheets to convert an imported YYYYMMDD text column into real dates with the built-in Text to Columns wizard, then apply a date format and widen the column to remove ####### errors.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook, and click the column that contains the YYYYMMDD text values.
- 2. Start Text to Columns: Click Data > Text to Columns, choose Delimited, and click Next twice.
- 3. Choose YMD date format: Under Column data format, select Date, choose YMD from the dropdown, and click Finish.
- 4. Apply the final date format: Select the converted cells, click Home > Number Format, and choose Short Date or Long Date.
- 5. Widen the column: If ####### appears, double-click the column boundary or click Home > Format > AutoFit Column Width.

Frequently Asked Questions
Why does my converted date still show ####### in WPS Spreadsheets?
The cell is not wide enough to display the date. Select the column and double-click the right edge of the column header, or go to Home > Format > AutoFit Column Width.
Can I use Text to Columns for YYYYMMDD numbers that are not stored as text?
Yes. Select the column, go to Data > Text to Columns, choose Delimited, click Next twice, select Date, choose YMD, and click Finish. WPS treats the existing numeric values as a YMD date sequence.
What formula can I use to convert YYYYMMDD text to a real date?
Use =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)), then fill down and format the result as Date from Home > Number Format.
Does this method work when YYYYMMDD includes January as 01 and the day as 03?
Yes. The YMD option preserves leading zeros for month and day during conversion, for example 20230103 becomes January 3, 2023.
How do I keep the original YYYYMMDD column while adding a converted date column?
Insert a blank helper column next to the source data and use the DATE formula method, or copy the source column before running Text to Columns so the original is preserved.




