Data Import & Export

How to Convert Imported Text Dates to Long Date Format in Excel

Kushani Nimanthika Oct 12, 2026 869 views

Question details

The user needs to convert dates imported into Excel that are currently behaving as text (e.g., 02/26/2024) into a functional long date format (e.g., Monday, February 26, 2024).

How to Convert Imported Text Dates to Long Date Format in Excel

Product: Excel
Device & OS: not provided
Scenario: Importing data into spreadsheets where the date values are improperly recognized as text strings.
Observed behavior: Imported dates act as text strings and do not respond to standard or custom date formatting options.

Before you start

Ensure your imported data column contains only dates and identify the original date format (such as Month-Day-Year or Day-Month-Year) before starting the conversion process.

Solution 1Recommended

Use Text to Columns and Apply Custom Formatting

The most effective way to convert text-formatted dates into real dates is by using the Text to Columns feature, then applying a custom long date format.

Excel often treats imported dates from CSV files or web pages as text. To make them respond to date formatting, you must first parse them into true serial numbers using the Text to Columns wizard.

Select the Data

Highlight the entire column containing the imported text dates that you want to convert.

Open Text to Columns

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

Configure the Wizard

In the wizard, choose 'Delimited' and click Next twice to reach Step 3 of 3.

Set the Date Format

Select the 'Date' option under Column data format. Choose the format that matches your imported text (e.g., MDY for Month-Day-Year) from the dropdown list, and click Finish.

Apply Long Date Custom Format

Right-click the selected column and choose 'Format Cells'. Go to the Custom category, enter 'dddd, mmmm d, yyyy' in the Type box, and click OK to display the long date.

Use Text to Columns and Apply Custom Formatting

Format successfully applied: Your dates will now display as 'Monday, February 26, 2024' and will correctly respond to formulas, sorting, and other date calculations.

Convert Data Easily

Easily Manage and Format Dates with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including an intuitive Text to Columns wizard, to seamlessly convert imported text dates into usable formats. Enjoy advanced data management for free.

1. Select the Dates: Open your file in WPS Spreadsheet and select the column containing the text dates.
2. Access Text to Columns: Go to the Data tab on the top menu and click on 'Text to Columns'.
3. Convert the Text: Follow the prompts, select Date and your specific format (like MDY) in the final step, then click Finish.
4. Format the Cells: Right-click the highlighted data, choose 'Format Cells', go to Custom, and type 'dddd, mmmm d, yyyy' to apply the long date format.

Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Built-in Text to Columns wizard for fast, accurate data conversion.Extensive custom date and time formatting options.Lightweight software with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my imported dates show up as text in Excel?

When importing data from external sources like CSVs or web pages, Excel may fail to recognize the delimiter or date structure based on your system's regional settings. To prevent data loss, it defaults to treating the unknown format as standard text.

Can I use formulas instead of Text to Columns to convert text dates?

Yes. You can use the DATEVALUE function to convert a text date into a date serial number. For example, typing =DATEVALUE(A1) converts the text in cell A1. You can then apply the custom long date format to the formula's result.

What if my imported dates are structured as DD/MM/YYYY instead of MM/DD/YYYY?

In the third step of the Text to Columns wizard, simply click the Date drop-down list and select 'DMY' instead of 'MDY'. This tells Excel exactly how to read and convert your specific source data structure.

How do I fix date conversion errors showing as #VALUE!?

The #VALUE! error usually occurs if the text string contains unrecognized characters (like extra spaces) or if the Date format chosen in Text to Columns doesn't match the actual structure of the text. Use the TRIM function to remove spaces, or ensure you selected the correct MDY/DMY/YMD format.

Excel & Data Problems