Formatting Issues

How to Fix Excel Dates That Are Not Recognized Correctly

Olivia MillerOlivia Miller Nov 3, 2026 873 views

Question details

Fix the issue where spreadsheet software treats entered dates as text rather than numeric serial values.

How to Fix Excel Dates That Are Not Recognized Correctly
Product
Excel
Device & OS
not provided
Scenario
Typing, importing, or pasting date data into a spreadsheet.
Observed behavior
Excel fails to recognize the dates, treating them as text. Autofill unexpectedly increases the year instead of the day, and changing the cell format to 'Date' does not fix the issue.
Before you start

Before attempting to fix the formatting, test a problematic cell (e.g., A10) by entering the formula =ISTEXT(A10) in an empty cell. If it returns TRUE, the date is indeed stored as text and needs to be converted.

Solution 1Recommended

Use Text to Columns to Convert Text to Dates

This is the most efficient and recommended way to convert an entire column of unrecognized text dates into valid numeric date values.

The Text to Columns feature can force Excel to parse text strings back into standard data types based on the date format you specify.

1
Select the Data

Highlight the entire column containing the dates that are not recognized correctly.

2
Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.

3
Navigate the Wizard

Ensure 'Delimited' is selected, then click 'Next' twice to reach Step 3 of the wizard.

4
Apply Date Format

In the 'Column data format' section, select the 'Date' radio button. From the dropdown, choose the structure that matches your text dates (e.g., MDY for Month-Day-Year). Click 'Finish'.

Use Text to Columns to Convert Text to Dates
Format Applied: The cells will now be converted to proper date serial numbers, and autofill will function correctly.
Manage Data Seamlessly

Easily Convert and Format Dates with WPS Spreadsheet

WPS Spreadsheet provides intuitive data handling tools, including Text to Columns and full support for advanced formulas like DATEVALUE, to help you resolve date formatting errors quickly without hassle.

  1. 1. Open Your Spreadsheet: Launch WPS Office and open the file with the unrecognized dates in WPS Spreadsheet.
  2. 2. Select the Problematic Column: Click the column header to highlight all the text-formatted dates.
  3. 3. Use Text to Columns: Go to the 'Data' tab on the ribbon and click 'Text to Columns'.
  4. 4. Complete the Conversion: Select 'Delimited', click Next until the final step, choose the 'Date' format matching your data layout, and click Finish.
Completely compatible with Microsoft Excel (.xlsx, .xls) filesLightweight, fast, and completely free to useIntuitive Text to Columns feature for instant date conversionFull support for all standard date and time formulas
microsoft office alternative - wps office

Frequently Asked Questions

Why does autofill increase the year instead of the day when dragging dates?

When Excel treats a date as text, it looks at the last number in the text string. For a date like '10/12/2023', the software sees '2023' at the end of a string and simply increments it to '2024', '2025', etc., instead of calculating calendar days.

Why isn't changing the cell format to 'Date' fixing the issue?

Cell formatting only changes the visual display of a cell's underlying value. If the underlying value is stored as text, applying a 'Date' format will have no effect because Excel doesn't recognize the text as a numeric date serial value.

What is a date serial number in Excel?

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. For example, January 1, 2024, is stored as 45292. When dates are formatted as text, this serial number system breaks.