Data Import & Export

How to Fix Excel Dates Recognized as Text or Showing Unexpected Numbers

Nimra Malik Oct 1, 2026 868 views

Question details

The user needs to correct Excel date entries that are incorrectly formatted as text or are displaying their underlying decimal serial numbers.

How to Fix Excel Dates Recognized as Text or Showing Unexpected Numbers

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing data or typing dates manually where Excel fails to apply standard date formatting.
Observed behavior: Dates appear as plain text, do not respond to format changes, or display as decimal values (e.g., 45470.3333) in the formula bar instead of standard date and time.

Before you start

Ensure your computer's system regional settings match the expected date format of your data (e.g., MM/DD/YYYY vs. DD/MM/YYYY) before attempting conversion, as regional mismatches often cause Excel to treat dates as text.

Solution 1Recommended

Use Text to Columns to Convert Text Dates

This is the most efficient method to force Excel to re-evaluate and convert a column of text-based dates into standard serial dates.

When dates are imported from other systems or copied from the web, they are often saved as plain text. The Text to Columns wizard quickly resolves this by parsing the text into a date format Excel can understand.

Select the Data

Highlight the entire column containing the dates that are currently recognized as text.

Open Text to Columns

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

Skip to the Date Step

In the wizard, ensure 'Delimited' is selected and click 'Next' twice to reach Step 3.

Apply Date Formatting

Select the 'Date' radio button and choose the format that matches your text data (e.g., MDY for Month-Day-Year). Click 'Finish'.

Use Text to Columns to Convert Text Dates

Instant Conversion: Your dates should now align to the right side of the cells, indicating Excel successfully recognizes them as numerical date values.

Solution 2

Change Cell Format for Decimal Serial Numbers

If you see a decimal number like 45470.33, Excel is displaying the raw date and time serial number. You simply need to apply a date-time format.

Solution 3

Remove Leading Apostrophes using DATEVALUE

Imported data often contains a hidden apostrophe (') before the date, forcing Excel to read it as text. Formulas can help clean this up in bulk.

Fix Date Formatting Issues Easily with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools like Text to Columns and customizable cell formatting to instantly resolve unrecognized text dates and decimal serial numbers. It seamlessly handles imported data and is fully compatible with Microsoft Excel files.

1. Open Your Spreadsheet: Launch WPS Office and open the file containing the problematic dates.
2. Identify the Problem: Highlight the column with text dates or unexpected decimal numbers.
3. Fix Decimal Numbers: If you see decimals, right-click, select 'Format Cells', and choose your preferred Date format from the Number tab.
4. Fix Text Dates: If dates are read as text, navigate to the 'Data' tab and click 'Text to Columns' to automatically convert them to date values.
5. Save Document: Save your work. The converted formatting remains perfectly compatible when opened in Excel.

Easily convert text to dates using the powerful Text to Columns wizardFully compatible with Microsoft Excel date serial numbers and custom formatsLightweight, fast, and completely free to use for everyday data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a 5-digit number instead of a date?

Excel stores dates as sequential serial numbers starting from January 1, 1900. For example, the number 45470 represents a specific day in 2024. If a cell's formatting is set to 'General' or 'Number', it will display this serial number instead of the formatted date. Simply right-click the cell, go to Format Cells, and change the format back to 'Short Date'.

What do the decimals after the date serial number mean?

The decimal portion of the serial number represents the time of day as a fraction of 24 hours. For example, .5 represents 12:00 PM (noon), and .3333 represents approximately 8:00 AM. To view both the date and time, apply a custom Date and Time format to the cell.

How can I fix dates that don't respond when I change the cell format?

If a date's appearance doesn't change when you apply a new date format, Excel is reading it as text rather than a numerical value. This frequently happens with imported data. You can resolve this by selecting the column, going to the Data tab, and using the 'Text to Columns' wizard to force Excel to parse the text back into a valid date.

Does the DATEVALUE function fix text dates?

Yes, the DATEVALUE function is specifically designed to convert a date represented as a text string into its corresponding Excel serial number. Once the function outputs the serial number, you just need to format that resulting cell as a standard date.

Excel & Data Problems