Fix Excel Date Serial Number Not Changing to mm/dd/yy Format
Question details
The user is unable to format Excel date serial numbers or date-like values into the standard mm/dd/yy date 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 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.
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.
Highlight the entire column containing the problematic dates.
Navigate to the 'Data' tab on the ribbon and click on 'Text to Columns'.
Choose 'Delimited' as the file type, then click 'Next' twice to reach Step 3 of the wizard.
Under 'Column data format', select the 'Date' radio button and choose the format that matches your current raw data (e.g., MDY).
Click 'Finish'. You can now go to the Home tab and apply your desired mm/dd/yy format successfully.
Use the DATEVALUE Function to Convert Text
If your dates are imported as stubborn text, the DATEVALUE function can parse them and convert them into valid Excel date serial numbers.
Adjust Regional Settings and Remove Hidden Characters
Imported data might contain hidden spaces or use a different regional date format than your system expects, preventing correct formatting.
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. Open your file: Launch WPS Office and open your spreadsheet containing the formatting issues.
- 2. Select the target cells: Highlight the cells that won't format correctly and navigate to the 'Data' tab.
- 3. Convert the data: Click on 'Text to Columns' to convert unrecognized text strings into standard Excel serial numbers.
- 4. Apply custom formatting: Right-click the cells, choose 'Format Cells', and apply the 'mm/dd/yy' custom date format from the Number tab.

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.




