How to Stop Excel from Changing 010123 into an Unexpected Date
Question details
When entering a 6-digit numerical string starting with zero (such as 010123), the user needs to prevent Excel from automatically interpreting it as a serial number and converting it into an incorrect, unexpected date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Typing a 6-digit numeric date code (like 010123 for January 1, 2023) directly into a standard spreadsheet cell.
- Observed behavior
- Excel drops the leading zero, reads the remaining number (10123) as a serial date code starting from January 1, 1900, and displays the unexpected date of September 18, 1927.
Determine whether your goal is to store '010123' exactly as a 6-digit text string or if you want Excel to properly recognize and format it as the date January 1, 2023.
Format Cells as Text Before Entering Data
Prevent Excel from treating your input as a number or serial date by explicitly formatting the cells as Text beforehand.
By default, Excel's 'General' format attempts to guess your data type. By applying the Text format before you type, Excel will store exactly what you enter, preserving leading zeros and preventing automatic date conversion.
Highlight the blank cell, range, or entire column where you plan to enter your 6-digit numbers.
Navigate to the Home tab on the ribbon. In the Number group, click the formatting dropdown menu (which usually says 'General').
Scroll to the bottom of the dropdown list and select 'Text'.
Type 010123 into the formatted cell. Excel will now keep the leading zero and display the exact string without altering it.
Convert Existing Numbers to Dates with Text to Columns
If Excel has already converted your inputs into serial numbers or incorrect dates, use the Text to Columns wizard to parse them into the correct date format.
Apply a Custom Number Format
Change the display rules of the cell so that a proper date is visually represented as a 6-digit string without slashes.
Manage and Format Dates Easily with WPS Spreadsheet
WPS Spreadsheet offers a familiar, highly compatible environment to manage your data without frustration. Features like Text to Columns, Custom Cell Formatting, and direct text entry work identically to Microsoft Excel, giving you full control over how your numbers and dates are displayed.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook where you need to input or fix your 6-digit codes.
- 2. Pre-format the data area: Highlight your target cells, go to the Home tab, and switch the number format dropdown from 'General' to 'Text'.
- 3. Fix existing errors: If the dates are already corrupted, select the column, navigate to Data > Text to Columns, and follow the simple wizard to define your MDY or DMY date structures.
- 4. Save with confidence: Save your document in your preferred format. WPS ensures the formatting remains perfectly intact when shared with Excel users.
Frequently Asked Questions
Why does Excel show 9/18/1927 when I type 010123?
When you enter 010123, Excel treats it as a number and automatically drops the leading zero, leaving 10123. Excel stores dates as serial numbers starting from January 1, 1900 (which is day 1). If you format 10123 as a date, Excel calculates 10,123 days after January 1, 1900, which lands exactly on September 18, 1927.
Can I use a formula to fix my date format?
Yes. If your cell is formatted as Text and contains '010123' (representing DD/MM/YY), you can extract the values to create a real date using the formula: =DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2)). You can adjust the LEFT, MID, and RIGHT parameters depending on whether your original text is in MDY or DMY order.
How do I force Excel to keep the leading zero without changing cell formatting?
You can type an apostrophe (') directly before the number (for example, '010123). Excel uses the apostrophe as an indicator that the following input should be treated strictly as text. The apostrophe won't print or display in the cell, but it preserves your leading zero and prevents automatic date conversion.
