Data Import & Export

How to Convert an Eight-Digit Text Value to a Date in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to convert downloaded dates that are currently formatted as continuous eight-digit text strings into valid Excel date formats.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using downloaded text string dates for aging calculations across multiple worksheets.
Observed behavior: Dates are downloaded into Excel as continuous text strings without separators (e.g., MMDDYYYY) rather than being recognized as real date values.

Before you start

Identify the exact format of your source text dates (e.g., MMDDYYYY vs. DDMMYYYY) before applying the formula to ensure the month and day components are extracted correctly.

Solution 1Recommended

Convert Text to Date Using the DATE Function

Extract the year, month, and day from the text string using the RIGHT, LEFT, and MID functions to construct a recognized date format.

The DATE function requires three arguments: Year, Month, and Day. By combining this with Excel's text extraction functions, you can slice an eight-digit text string into these three required pieces.

Select a blank cell

Click on a blank cell where you want the converted date to appear (e.g., cell B2).

Enter the DATE formula

Assuming your eight-digit text (in MMDDYYYY format) is in cell A2, type the following formula: =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,3,2))

Apply the formula

Press Enter to execute the formula. Excel will combine the extracted text components into a real date.

Format as Date

If the result appears as a standard 5-digit serial number, right-click the cell, select 'Format Cells', choose 'Date' under the Number tab, and pick your preferred date format.

Adjusting for different formats: If your source data is in YYYYMMDD format, adjust the formula to =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)).

Solution 2

Convert Text to Date Using Text to Columns

Use the built-in Text to Columns tool to quickly reformat a whole column of text values into dates without typing formulas.

Effortless Data Management with WPS

Easily Convert Text Strings to Dates in WPS Spreadsheet

WPS Spreadsheet fully supports standard formulas like DATE, LEFT, MID, and RIGHT, as well as the powerful Text to Columns tool. You can seamlessly convert and format your downloaded text dates for accurate calculations in an intuitive interface.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your downloaded text dates.
2. Enter the conversion formula: Select the blank cell next to your text date and enter =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,3,2)).
3. Format the output: Press Enter, then right-click the cell to select 'Format Cells' and apply a standard Date format.
4. Apply to the entire column: Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the rest of the column.

Fully compatible with Microsoft Excel formulas and date formats (.xlsx, .xls).Built-in Text to Columns wizard for instant date conversion.Lightweight, fast, and free to use for everyday data analysis.

Frequently Asked Questions

Why does my converted date show as a random 5-digit number?

Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If you see a 5-digit number like 44197, simply select the cell, right-click, choose 'Format Cells', and apply a 'Date' format to display it correctly.

How do I convert a DDMMYYYY text string to a date?

You can adjust the DATE formula to extract the components in the correct order. For a DDMMYYYY string in cell A2, use =DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2)) to correctly map the year, month, and day.

Can I convert text to dates without using a formula?

Yes, you can use the 'Text to Columns' feature found under the Data tab. In the third step of the wizard, select 'Date' and choose the format pattern that matches your original text string (like MDY).

Excel & Data Problems