Formatting Issues

How to Fix Excel Dates Recognized as Text or General Values

WPS Content Manager Oct 7, 2026 870 views

Question details

Users need to resolve an issue where valid date formats are misinterpreted as text or general values, preventing proper date sorting and calculations.

How to Fix Excel Dates Recognized as Text or General Values

Product: Excel
Device & OS: Windows
Scenario: Importing or entering date data from a system with different regional formats, resulting in unrecognized date strings.
Observed behavior: Excel displays dates such as 12 Sep 2023 or 8/21/2023 as plain text or General values, ignoring date formatting attempts.

Before you start

Check your data for hidden characters, leading spaces, or a preceding apostrophe ('), as these will automatically force Excel to treat numbers and dates as text.

Solution 1Recommended

Adjust Windows Regional Settings

Modify your system-wide regional settings so Excel can correctly interpret the imported date formats based on the source region.

Excel relies heavily on the Windows operating system's regional settings to interpret what constitutes a valid date. If a spreadsheet was created on a computer with a different date layout (e.g., DD/MM/YYYY vs MM/DD/YYYY), Excel might fail to recognize the values as dates.

Close Microsoft Excel

Save your work and completely exit the Excel application to ensure new settings take effect.

Open Windows Control Panel

Click the Windows Start menu, type 'Control Panel', and press Enter.

Navigate to Region Settings

Click on 'Clock and Region', then select 'Region'.

Change the Date Format

Under the 'Formats' tab, locate the 'Short date' and 'Long date' dropdowns. Change them to match the format of the text dates in your spreadsheet.

Apply and Restart Excel

Click 'Apply' and then 'OK'. Reopen Excel and your dates should now be properly recognized.

System-wide Impact: Changing this setting affects how dates are displayed across all Windows applications, not just Excel.

Solution 2

Convert Text to Dates Using Text to Columns

Use a built-in Excel data tool to convert text dates into actual date values without altering your computer's global regional settings.

Solution 3

Use the DATEVALUE Function

Extract actual date values from text using an Excel formula, which is ideal if you want to keep the original text data intact while performing calculations.

Seamless Data Management

Easily Format and Manage Dates with WPS Spreadsheet

WPS Office provides robust data handling tools, fully compatible with Microsoft Excel formats, allowing you to quickly convert, format, and calculate dates without regional compatibility issues.

1. Open Your File in WPS: Launch WPS Spreadsheet and open the document containing the unrecognized dates.
2. Select Problematic Cells: Highlight the column where the dates are being treated as text.
3. Use Data Tools: Go to the 'Data' tab on the top ribbon and select 'Text to Columns'.
4. Convert to Date: In the wizard, specify the current format of your dates to instantly convert them into proper date values.

Built-in Text to Columns tool for quick and accurate date conversionFully compatible with Microsoft Excel (.xlsx and .xls) formatsAdvanced cell formatting for precise date and time displaysLightweight and fast, even when processing large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my date to a 5-digit number?

Excel stores dates as sequential serial numbers so they can be used in calculations (e.g., January 1, 1900, is serial number 1). If you see a 5-digit number instead of a date, your cell is formatted as 'General' or 'Number'. Simply change the cell format to 'Date' via the Home tab to display it correctly.

Can I change the default date format in Excel without changing Windows settings?

Excel pulls its default date format directly from your Windows Regional settings. You cannot change the default for all new Excel workbooks independently of Windows. However, you can manually apply custom date formats to specific cells or columns using the 'Format Cells' dialog within Excel.

Why doesn't changing the cell format from Text to Date fix my problem?

If a cell was originally formatted as Text when the date was typed or imported, simply changing the dropdown format to 'Date' will not re-evaluate the underlying data. You must force Excel to refresh the cell by double-clicking it and pressing Enter, or by using the 'Text to Columns' feature to perform a bulk conversion.

How do I fix dates that include time (e.g., 8/21/2023 8:30:45 AM) recognized as text?

You can use the 'Text to Columns' tool to parse the date, or use the VALUE function. Entering =VALUE(A1) into a new cell converts a recognized date-time text string into a decimal serial number. You can then format that new cell as a Custom Date and Time format (e.g., m/d/yyyy h:mm AM/PM).

Document & File Issues