Data Import & Export

How to Fix Excel Not Formatting Copied Dates as MMDDYYYY

John Wilson Sep 28, 2026 869 views

Question details

The user needs to correctly format dates copied from Notepad into Excel, as the application currently treats them as text or displays hash marks instead of the desired MMDDYYYY format.

How to Fix Excel Not Formatting Copied Dates as MMDDYYYY

Product: Excel
Device & OS: not provided
Scenario: Pasting date records from a plain text editor (Notepad) into an Excel spreadsheet and attempting to apply a custom date format.
Observed behavior: Copied dates are treated as text or invalid values, and applying a date format produces incorrect dates or hash marks (####) rather than formatting as MMDDYYYY.

Before you start

Ensure the column containing your dates is wide enough, as a series of hash marks (####) in Excel often indicates that the column is too narrow to display a properly formatted date.

Solution 1Recommended

Use Text to Columns to Convert Text to Dates

This is the most effective method to force Excel to recognize plain text dates as valid serial dates, allowing you to format them however you want.

When data is pasted from plain text editors like Notepad, Excel often interprets it as a text string rather than a date value. Changing the cell format alone won't work on text strings. You must first parse the text into a date value using the Text to Columns wizard.

Select the target column

Highlight the entire column or the specific range of cells containing the unformatted dates copied from Notepad.

Open Text to Columns wizard

Navigate to the 'Data' tab on the Excel ribbon and click the 'Text to Columns' button located in the Data Tools group.

Navigate to the final step

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

Set column data format

Under the 'Column data format' section, select the 'Date' radio button. From the adjacent dropdown menu, select the format that matches the original layout of your copied text (for example, 'MDY' for Month-Day-Year), then click 'Finish'.

Apply MMDDYYYY format

Right-click the newly converted cells and select 'Format Cells'. Go to the 'Custom' category, type 'mmddyyyy' into the 'Type' field, and click 'OK'.

Use Text to Columns to Convert Text to Dates

Conversion complete: Your text strings are now actual Excel date values and will correctly display in the MMDDYYYY layout.

Solution 2

Adjust Column Width for Hash Marks

Use this solution if your dates were actually recognized correctly but are displaying as #### due to display constraints.

Seamless Data Formatting

Easily Format Imported Data with WPS Spreadsheet

If you frequently work with plain text data imports, WPS Spreadsheet provides a highly compatible and intuitive environment to quickly convert and format text dates using the Text to Columns feature, exactly like Microsoft Excel.

1. Paste Notepad Data: Copy your dates from Notepad and paste them directly into a WPS Spreadsheet column.
2. Access Text to Columns: Select the pasted data, navigate to the 'Data' tab, and click 'Text to Columns'.
3. Convert to Date: Proceed to the final step of the wizard, choose 'Date', select the source text structure (e.g., MDY), and click 'Finish'.
4. Apply Custom Format: Right-click the converted cells, choose 'Format Cells', select 'Custom', and input 'mmddyyyy' to format the dates perfectly.

Seamless compatibility with Microsoft Excel (.xlsx, .csv) formatsIntuitive Text to Columns wizard for fast text-to-date conversionFully supports custom date formatting, including MMDDYYYYLightweight, free to use, and loads large datasets quickly

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show #### instead of my dates?

Excel displays hash marks (####) when a cell contains a valid date or number, but the column width is too narrow to display the entire sequence. You can fix this by double-clicking the right edge of the column header to auto-fit the contents.

Why won't formatting change the appearance of my pasted text?

If data is pasted as text, Excel treats it as a text string rather than a numerical date value. Formatting options (like changing date displays) only work on valid numerical values, meaning you must first convert the text to dates using the Text to Columns tool.

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

Yes, you can use the DATEVALUE function. For example, entering =DATEVALUE(A1) will convert a recognizable text date in cell A1 into a serial number. You can then format that formula cell as a date with MMDDYYYY.

How do I create a custom date format in Excel?

Select the cells you want to format and press Ctrl+1 to open the Format Cells dialog. Under the Number tab, select 'Custom' from the category list, type your desired layout (like 'mmddyyyy') into the Type box, and click OK.

Excel & Data Problems