Formatting Issues

Fix Excel Date Serial Number Not Changing to mm/dd/yy Format

Chanuka GeekiyanageChanuka Geekiyanage Nov 10, 2026 870 views

Question details

The user is unable to format Excel date serial numbers or date-like values into the standard mm/dd/yy date format.

How to Fix Excel Date Serial Numbers Not Changing to mm/dd/yy Format
Product
Excel
Device & OS
not provided
Scenario
Attempting to format spreadsheet cells containing date serial numbers or imported date strings into a specific date format.
Observed behavior
Changing the cell format to mm/dd/yy has no effect on the displayed value, often because the data is stored as text or contains hidden characters.
Before you start

Before troubleshooting, widen the column to ensure the dates aren't just hidden by a narrow column width, and double-click the cell to check if it contains any leading apostrophes forcing it to act as text.

Solution 1Recommended

Convert Text Dates to Real Dates Using Text to Columns

Use the Text to Columns feature to quickly force Excel to recognize text strings as actual date serial numbers so you can format them.

Often, imported data looks like a date but is treated by Excel as a text string. The Text to Columns tool is the fastest way to re-evaluate the column data and convert it into true date serial numbers.

1
Select the data

Highlight the entire column containing the problematic dates.

2
Open Text to Columns

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

3
Skip to the final step

Choose 'Delimited' as the file type, then click 'Next' twice to reach Step 3 of the wizard.

4
Set the date format

Under 'Column data format', select the 'Date' radio button and choose the format that matches your current raw data (e.g., MDY).

5
Finish and reformat

Click 'Finish'. You can now go to the Home tab and apply your desired mm/dd/yy format successfully.

Quick Verification: You can test if a cell is a true date by changing its format to 'General'. A true date will immediately change to a 5-digit serial number (e.g., 44197).
Advanced Data Tools

Easily Manage and Format Spreadsheet Dates with WPS Office

WPS Spreadsheet offers powerful data conversion tools and seamless compatibility with Microsoft Excel files, making it easy to fix formatting errors, convert text to dates, and manage large datasets efficiently.

  1. 1. Open your file: Launch WPS Office and open your spreadsheet containing the formatting issues.
  2. 2. Select the target cells: Highlight the cells that won't format correctly and navigate to the 'Data' tab.
  3. 3. Convert the data: Click on 'Text to Columns' to convert unrecognized text strings into standard Excel serial numbers.
  4. 4. Apply custom formatting: Right-click the cells, choose 'Format Cells', and apply the 'mm/dd/yy' custom date format from the Number tab.
100% compatible with Microsoft Excel formats (.xlsx, .xls)Built-in Text to Columns tool for quick data conversionIntuitive cell formatting interface for custom date formatsFree, lightweight, and fast performance on all devices
microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel dates show up as 5-digit numbers?

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. If you see a 5-digit number, your cell format is likely set to 'General' or 'Number' instead of 'Date'.

How can I tell if an Excel date is stored as text?

By default, text is left-aligned in a cell, while valid dates (which are numbers) are right-aligned. You can also try changing the cell format to 'General'; if the value doesn't change to a 5-digit serial number, it is stored as text.

Will removing leading apostrophes fix my date format?

Yes, a leading apostrophe forces Excel to treat the cell contents as text. Removing the apostrophe allows Excel to recognize the entry as a date, after which you can apply your desired mm/dd/yy format.

What if my dates have Unicode dashes instead of standard hyphens?

Excel may not recognize non-standard dashes as date separators. You can use the 'Find and Replace' tool (Ctrl+H) to replace Unicode dashes with standard hyphens or forward slashes to make them recognizable dates.