Data Import & Export

Fix Excel Dates Not Formatting as MMDDYYYY

Maira Mehtab Sep 22, 2026 868 views

Question details

The user is unable to format dates copied from a text file (such as Notepad) into Excel as MMDDYYYY because the application treats the imported values as text.

Product: Excel
Device & OS: not provided
Scenario: Importing or copying date records from a plain text editor into a spreadsheet and attempting to apply custom date formats.
Observed behavior: Formatting the cells as dates fails, resulting in the values remaining as text, displaying hash marks, or generating incorrect date entries.

Before you start

Ensure the column containing your dates is wide enough to display the full text, as hash marks (###) can sometimes simply indicate a narrow column rather than a data format error.

Solution 1Recommended

Convert Text to Dates Using Text to Columns

This method parses imported text strings into actual date values, allowing you to successfully apply custom date formats like MMDDYYYY.

When data is copied from plain text editors, spreadsheets often paste the entries as text strings rather than recognizing them as date values. Date formats cannot be applied to text, which is why formatting attempts fail or produce incorrect results.

Select the data

Highlight the entire column or the specific cells containing the problematic text dates.

Open Text to Columns

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

Navigate the wizard

Select 'Delimited' in the first step and click 'Next' twice to reach Step 3 of the wizard.

Define the date format

In the 'Column data format' section, select 'Date' and choose the sequence from the dropdown that matches your original text (e.g., MDY for Month-Day-Year).

Finish conversion

Click 'Finish' to convert the text strings into real date values.

Apply MMDDYYYY format

Right-click the converted cells, select 'Format Cells', go to the 'Custom' category, and type 'MMDDYYYY' into the format box to apply your desired visual format.

Format Applied Successfully: Once your data is converted to valid date values, you can change the visual format at any time without affecting the underlying chronological data.

Format Dates Seamlessly

Easily Convert and Format Dates in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools, including an intuitive Text to Columns wizard and advanced custom cell formatting, making it simple to manage imported date text.

1. Open your file: Launch WPS Spreadsheet and select the column containing the text dates.
2. Launch Text to Columns: Go to the Data tab and select Text to Columns.
3. Convert to Date: Follow the wizard to Step 3, choose the Date format corresponding to your raw data, and click Finish.
4. Apply Custom Format: Press Ctrl+1 to open the Format Cells dialog, select Custom, type MMDDYYYY, and click OK.

Quickly convert imported text to dates with the Text to Columns tool.Create and apply custom MMDDYYYY formats with just a few clicks.Fully compatible with Microsoft Excel (.xlsx) files and data formats.Lightweight, fast, and free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I see hash marks (###) after converting my dates?

Hash marks usually indicate that the column is too narrow to display the fully formatted date. You can fix this by double-clicking the boundary on the right side of the column header to auto-fit the column width.

Can I use a formula to convert text dates to real dates?

Yes, you can use the DATEVALUE function. For example, typing '=DATEVALUE(A1)' in an empty cell will convert a recognizable text date in cell A1 into a date serial number, which you can then format as MMDDYYYY.

What if the Text to Columns wizard produces completely incorrect dates?

This happens when the date format you selected in Step 3 of the wizard does not match the layout of your original text. Make sure you select the correct sequence (such as MDY or DMY) that reflects how the data is written in your source file.

Excel & Data Problems