Data Import & Export

Why Excel Converts 231024 to the Wrong Date and How to Fix It

Maira Mehtab Sep 20, 2026 869 views

Question details

Users notice that when they type a 6-digit number like 231024 into Excel and apply a date format, it incorrectly converts to a distant future date (like 24/02/2531) instead of the intended day, month, and year.

Product: Excel
Device & OS: not provided
Scenario: Entering dates as continuous 6-digit numbers without using any separators.
Observed behavior: Excel treats the 6-digit value as a numeric serial date (counting the number of days since January 1, 1900) instead of intelligently splitting the digits into a day, month, and year.

Before you start

Determine whether you need to enter new dates correctly moving forward, or if you need to convert an existing column of 6-digit numbers into proper dates.

Solution 1Recommended

Enter Dates Using Proper Separators

The easiest way to prevent Excel from interpreting your input as a serial number is to type dates with standard separators.

Excel relies on standard formatting cues to recognize dates. Without separators, it assumes the number you typed represents a mathematical value or a serial day count.

Use slashes or hyphens

Instead of typing '231024', type '23/10/24' or '23-10-2024' directly into the cell.

Pre-format the cell range

Select the cells where you plan to enter dates. Right-click, choose 'Format Cells', select 'Date' from the Number tab, and choose your preferred regional format before you begin typing.

Tip: Consistently using standard separators prevents Excel from executing automatic, unintended serial conversions.

Solution 2

Convert Existing 6-Digit Numbers Using a Formula

If you already have a large dataset of 6-digit numbers, you can use text functions to explicitly extract the day, month, and year.

WPS Spreadsheet Solution

Easily Manage and Format Dates with WPS Spreadsheet

WPS Spreadsheet offers intuitive date recognition and powerful formula support to help you manage your data without the headache of serial number conversions.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Pre-format your columns: Select your target column, right-click and choose 'Format Cells'. Set the category to 'Date'.
3. Enter data safely: Type your dates using standard separators (e.g., 23/10/24) to ensure they are interpreted correctly.
4. Convert bulk data: Use built-in tools like 'Text to Columns' under the Data tab to quickly split and convert existing unformatted numbers into proper dates.

Fully compatible with Microsoft Excel file formats (.xlsx and .xls)Supports all standard date formulas and formatting optionsLightweight, fast, and completely free to useFamiliar user interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel use a serial number system for dates?

Excel calculates dates by counting the number of days that have passed since January 1, 1900. When you enter a large 6-digit number like 231024 without separators, Excel assumes you mean the 231,024th day after that starting point, which lands far into the future.

Can I force Excel to automatically insert slashes as I type a 6-digit number?

Excel doesn't natively insert slashes to create real dates from pure numbers automatically. However, you can use Custom Formatting (like ##"/"##"/"##) to make a 6-digit number visually look like a date, though its underlying value will still remain a regular number rather than a true date.

Is there a way to fix an entire column of these wrong dates without formulas?

Yes. If your data was imported as 6-digit numbers, you can select the column, go to the Data tab, and click 'Text to Columns'. Proceed to the third step of the wizard, select 'Date', choose the correct format sequence (like DMY), and click Finish to instantly convert them.

Excel & Data Problems