How to Convert 8-Digit Numbers to Date Format in Excel
Question details
The user needs to convert an 8-digit continuous number string into a standard readable date format with slashes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing or cleaning imported data where dates appear as a continuous string of eight digits without any separators.
- Observed behavior
- The data is currently displayed as continuous numbers (e.g., 01232023) instead of a recognized date format (e.g., 01/23/2023).
Ensure your 8-digit numbers are formatted consistently (e.g., all in MMDDYYYY format) before applying conversion formulas or tools to avoid incorrect date outputs.
Convert to Date Format Using the MID Formula
Extract and combine the month, day, and year using the MID function to format the string exactly as needed.
The MID function is highly effective for extracting specific characters from a text string. By extracting the month, day, and year separately and concatenating them with a slash separator, you can seamlessly convert continuous numbers into readable dates.
Select a blank cell adjacent to your 8-digit value. For example, if your 8-digit number is in cell A1, select cell B1.
Type the formula =MID(A1,1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,4) into the cell and press Enter.
Click on the cell containing the new formula, hover over the bottom-right corner to find the fill handle, and drag it down to convert the rest of the values in your column.
Convert to Date Format Using Text to Columns
Use Excel's built-in Text to Columns feature to quickly convert a whole column of unformatted numbers into standard dates without needing formulas.
Format Dates Quickly in WPS Spreadsheet
Easily clean up imported data and convert numbers into standard date formats seamlessly using WPS Spreadsheet. It offers full support for data processing tools and is completely compatible with Excel formats.
- 1. Open your dataset: Launch WPS Spreadsheet, open your document, and select the column filled with 8-digit numbers.
- 2. Use Text to Columns: Go to the Data tab on the top menu and select 'Text to Columns'.
- 3. Select Date format: Click 'Next' until you reach the final step, check 'Date', select 'MDY', and click 'Finish' to apply the conversion.
Frequently Asked Questions
Why does my date formula return a #VALUE! error?
This usually happens if the referenced cell contains hidden spaces or non-numeric characters. Use the TRIM function around your cell reference, such as =TRIM(A1), to remove extra spaces before applying the MID formula.
Can I convert dates formatted as DDMMYYYY instead?
Yes, you can easily adjust the extraction logic. In the Text to Columns wizard, select 'DMY' instead of 'MDY'. If using the formula, rearrange the MID segments to place the day first, or keep the same formula if you want the final text string to follow the DD/MM/YYYY structure.
How do I change the slashes to dashes (01-23-2023)?
To use dashes instead of slashes, simply replace the slash ("/") with a dash ("-") in your MID formula. The updated formula will look like this: =MID(A1,1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,4).
