Convert Excel Value 080120 to August 1, 2020 in WPS Spreadsheets
Question details
User needs to convert a six-digit value such as 080120 into a recognizable date, August 1, 2020, after the spreadsheet application strips the leading zero and interprets the remaining digits as an Excel serial date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Using a spreadsheet that contains six-digit date values in MMDDYY order, such as 080120 for August 1, 2020.
- Observed behavior
- The application removes the leading zero, stores the value as 80120, and interprets it as the serial date May 11, 2119. Number formatting alone cannot convert it to the intended date.
Confirm that the source values are consistent six-digit entries in MMDDYY order, such as 080120 for August 1, 2020. Keep a blank helper column available for the formula results so the original data remains unchanged.
Convert 080120-style values with TEXT, LEFT, MID, RIGHT, and DATE
Use this formula when you need a repeatable, non-destructive conversion for six-digit MMDDYY values in WPS Spreadsheets.
WPS Spreadsheets stores 080120 as the numeric value 80120, so a plain Date number format cannot restore the missing leading zero or infer the intended month-day-year layout. The formula uses TEXT("000000") to rebuild a six-digit text string, then LEFT, MID, and RIGHT extract the month, day, and year components for the DATE function.
Open WPS Spreadsheets and click the cell next to your first value, for example B2, so the formula result has a clear location.
Type =DATE(2000+RIGHT(TEXT(A2,"000000"),2),LEFT(TEXT(A2,"000000"),2),MID(TEXT(A2,"000000"),3,2)) and press Enter. Replace A2 with the actual source cell if needed.
Select B2 and drag the fill handle down through all rows that contain six-digit values.
With the result cells selected, press Ctrl+1, choose Date, and select the format that shows August 1, 2020 or 8/1/2020.
Convert 080120 values into real dates in WPS Spreadsheets
Use WPS Spreadsheets' built-in formula support to split and rebuild six-digit date values with the DATE function, then format them as August 1, 2020. The resulting .xlsx workbook remains fully compatible with Microsoft Excel.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xls file, and click the worksheet containing the 080120 values.
- 2. Select a blank helper cell: Click the first empty cell next to the source value, such as B2, where the converted date will appear.
- 3. Enter the conversion formula: Type =DATE(2000+RIGHT(TEXT(A2,"000000"),2),LEFT(TEXT(A2,"000000"),2),MID(TEXT(A2,"000000"),3,2)) and press Enter.
- 4. Fill the formula down: Drag the fill handle from B2 through the remaining rows in the data range to convert all values.
- 5. Apply a date format: Select the converted cells, right-click, choose Format Cells, click Date, and select a display format such as August 1, 2020.
Frequently Asked Questions
Why does WPS Spreadsheets show May 11, 2119 when I enter 080120?
WPS Spreadsheets removes the leading zero and stores the number 80120. Because the application uses a serial date system, 80120 corresponds to May 11, 2119. It cannot infer that you meant August 1, 2020 without a formula.
Can I just apply a Date number format to 80120?
No. Number formatting only changes the display of the underlying serial number, so 80120 still evaluates as May 11, 2119. You must first reconstruct the intended year, month, and day with a formula such as DATE.
What changes are needed if the value is in YYMMDD order instead of MMDDYY?
Swap the MID and LEFT arguments: =DATE(2000+LEFT(TEXT(A2,"000000"),2),MID(TEXT(A2,"000000"),3,2),RIGHT(TEXT(A2,"000000"),2)).
How do I keep the leading zero before conversion?
Format the source cells as Text before entering or importing values, or use TEXT(A2,"000000") inside the formula to restore the missing leading zero for existing numeric values.
