Convert Variable-Length General Numbers into Excel Dates in WPS Spreadsheets
Question details
The user needs to convert exported numeric date codes of varying length, such as 202311 or 2023121, into real Excel dates by splitting the year and month parts.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- After a data export, date fields are stored as general numeric values instead of dates.
- Observed behavior
- The raw values do not display as dates and need to be split into year and month before Excel can recognize them.
Confirm whether the exported values follow the 6-digit year-month pattern or the 7-digit year-month-day pattern, and insert an empty helper column next to the source data. Test the conversion formula on several rows before filling it down.
Convert variable-length numbers with the LEN and DATE formula
Use this one-formula method when all exported values follow the 6-digit year-month or 7-digit year-month-day pattern.
The formula reads the first four digits as the year. It then checks the value length: a 6-digit value uses the last two digits as the month, while a 7-digit value uses the fifth and sixth digits as the month. The day is set to 1.
Click the first empty cell next to your exported date number, such as B2 if the raw value is in A2.
Type =DATE(LEFT(A2,4),IF(LEN(A2)=6,RIGHT(A2,2),MID(A2,5,2)),1) and press Enter.
Select the result cell, press Ctrl+1, choose Date in the Number tab, select a date format, and click OK.
Drag the fill handle from the bottom-right corner of B2 down the column or double-click it to fill the remaining rows.
Convert exported date numbers with WPS Spreadsheets
Open your exported workbook in WPS Spreadsheets and use a single DATE and LEN formula to split the variable-length number into year and month, then format the result as a real date. You can apply the formula down the column without changing the original data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the file containing the exported date numbers, and locate the source column.
- 2. Enter the conversion formula: In the first helper cell, enter =DATE(LEFT(A2,4),IF(LEN(A2)=6,RIGHT(A2,2),MID(A2,5,2)),1) and press Enter.
- 3. Apply a Date format: Select the formula cell, press Ctrl+1, click Date, choose the desired date format, and click OK.
- 4. Fill the formula down the column: Drag the fill handle or double-click it to apply the formula to the remaining rows.
Frequently Asked Questions
Can I convert an entire column of variable-length numbers at once?
Yes. Enter the formula in the first helper cell, press Enter, then drag the fill handle down or double-click it to fill the formula across the remaining rows.
How do I change the formula result from a number to a date format?
Select the converted cells, press Ctrl+1, open the Number tab, choose Date, select a date format, and click OK.
Does this formula work in Microsoft Excel too?
Yes. The formula uses standard functions such as DATE, LEFT, RIGHT, MID, and LEN, so it works in both WPS Spreadsheets and Microsoft Excel.
What if my exported value is a 5-digit number, such as 20231?
If the pattern is a 4-digit year followed by a 1-digit month, use =DATE(LEFT(A2,4),MID(A2,5,1),1) and format the result as a date.
What should I do if some cells contain text or missing values?
The formula may return an error for non-numeric entries. Test it on a sample of rows first and clean blanks or text values before filling the full column.
