Fix Excel Formula That Converts an Eight-Digit Value to a Date in WPS Office
Question details
The user needs to correct an Excel formula that converts an eight-digit value such as 20220502 into a valid date by extracting the year, month, and day segments.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an eight-digit value such as 20220502 in a worksheet cell and needs it to appear as a valid Excel date, for example 2022-05-02.
- Observed behavior
- The current formula does not return the expected date, typically because the month extraction starts at character 3 instead of character 5 and the formula is missing a closing parenthesis.
Before editing the formula, verify that the source value follows a consistent YYYYMMDD format and identify the correct cell reference, such as A221.
Correct the DATE formula with LEFT, MID, and RIGHT
Use this solution when the eight-digit value is being parsed incorrectly or the formula returns an error instead of a valid date.
The DATE function creates a valid date from separate year, month, and day values. In an eight-digit YYYYMMDD value, the month segment starts at character 5, not character 3.
Click the cell where you want the converted date to appear, such as B221.
Type =DATE(LEFT(A221,4),MID(A221,5,2),RIGHT(A221,2)) and press Enter.
Make sure the MID function uses 5 as the start position so it reads characters 5 and 6 as the two-digit month.
With the result cell selected, go to Home > Number Format and choose Short Date or Long Date.
Alternative: Convert with TEXT and DATEVALUE
Use this alternative when you want a shorter formula and the source cell is already stored as an eight-digit number or text in YYYYMMDD format.
Correct the Excel Date Formula in WPS Office Spreadsheets
Open the affected workbook in WPS Office Spreadsheets and use the DATE(LEFT(A221,4),MID(A221,5,2),RIGHT(A221,2)) formula to convert an eight-digit YYYYMMDD value into a valid date, then format the result cell as a date.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the eight-digit date value.
- 2. Enter the corrected DATE formula: In a blank cell, type =DATE(LEFT(A221,4),MID(A221,5,2),RIGHT(A221,2)) and press Enter.
- 3. Apply a date format: Select the result cell, click Home > Number Format, and choose Short Date or Long Date.
- 4. Save the workbook: Save the file as .xlsx to keep the formula and date formatting compatible with Excel.
Frequently Asked Questions
Why does my existing formula return #VALUE! when converting 20220502?
The most common cause is that the MID function starts at character 3 instead of character 5, so it reads the wrong month segment. Use =DATE(LEFT(A221,4),MID(A221,5,2),RIGHT(A221,2)) and confirm the source cell contains exactly eight digits.
How do I display the result as a date instead of a number?
Select the result cell, go to Home > Number Format, and choose Short Date or Long Date. Excel stores dates as serial numbers, so the date format must be applied manually.
Can I use this formula for a whole column of eight-digit values?
Yes. Enter the formula in the first row and drag the fill handle down to copy it. The cell references update automatically.
Does this formula work in both WPS Office and Microsoft Excel?
Yes. DATE, LEFT, MID, and RIGHT are standard Excel-compatible functions and are supported in WPS Office Spreadsheets.
What if my eight-digit value has leading zeros or inconsistent spacing?
Store the value as text in a consistent YYYYMMDD format. If needed, use TEXT(A221,"00000000") to normalize leading zeros before applying the DATE formula.
