Data Import & Export

Convert YYYYMMDD Text to a Date in WPS Spreadsheets and Fix ####### Errors

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 869 views

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.

Convert YYYYMMDD Text to a Date in WPS Spreadsheets and Fix ####### Errors
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the YYYYMMDD column

Click the column letter at the top of the sheet to select the entire column that contains the YYYYMMDD text values.

2
Open Text to Columns

Go to the Data tab in the WPS Spreadsheets ribbon and click Text to Columns.

3
Choose Delimited and move through the wizard

In the Text to Columns wizard, select Delimited and click Next twice to reach the Column data format step.

4
Set the date format to YMD

Under Column data format, choose Date, open the format drop-down list, select YMD, and click Finish.

5
Apply a date format and fix #######

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 Text to Columns to convert YYYYMMDD text to dates
Dates are ready to use: If the cell still shows #######, widen the column by double-clicking the right edge of the column header or using AutoFit Column Width.
WPS Spreadsheets

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. 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. 2. Start Text to Columns: Click Data > Text to Columns, choose Delimited, and click Next twice.
  3. 3. Choose YMD date format: Under Column data format, select Date, choose YMD from the dropdown, and click Finish.
  4. 4. Apply the final date format: Select the converted cells, click Home > Number Format, and choose Short Date or Long Date.
  5. 5. Widen the column: If ####### appears, double-click the column boundary or click Home > Format > AutoFit Column Width.
Converts YYYYMMDD text to real dates without formulasFully compatible with Microsoft Excel .xlsx and .xls date formatsFamiliar ribbon and Data tab make migration easyFree, lightweight Office suite for Windows, macOS, Linux, Android, and iOS
microsoft office alternative - wps office

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.