How to Fix Excel Dates That Are Not Recognized Correctly
Question details
Fix the issue where spreadsheet software treats entered dates as text rather than numeric serial values.

- 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 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.
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.
Highlight the entire column containing the dates that are not recognized correctly.
Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
Ensure 'Delimited' is selected, then click 'Next' twice to reach Step 3 of the wizard.
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 the DATEVALUE Function
Best for extracting actual date values from text using a helper column, especially when dealing with complex datasets.
Check Windows Regional Date Settings
If new dates you type manually are not recognized, your system's regional settings might conflict with how you type dates.
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. Open Your Spreadsheet: Launch WPS Office and open the file with the unrecognized dates in WPS Spreadsheet.
- 2. Select the Problematic Column: Click the column header to highlight all the text-formatted dates.
- 3. Use Text to Columns: Go to the 'Data' tab on the ribbon and click 'Text to Columns'.
- 4. Complete the Conversion: Select 'Delimited', click Next until the final step, choose the 'Date' format matching your data layout, and click Finish.

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.




