How to Convert Text to Date Format in Excel (Fix Dates Not Formatting)
Question details
The user needs to change the date format of specific cells, but the formatting options are not applying because the data is stored as text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to change the visual format of dates in a spreadsheet using the Format Cells option.
- Observed behavior
- Changing the date format has no effect because Excel recognizes the cell values as text strings instead of actual date serial numbers.
Before proceeding, check if your dates are aligned to the left of the cell, which usually indicates they are stored as text, and ensure the column is wide enough to display the new format.
Use Text to Columns to Convert Text Strings to Real Dates
The Text to Columns wizard is the quickest and most reliable method to force Excel to evaluate text strings as proper date values.
When Excel imports data from other systems or receives copy-pasted values, dates are often formatted as plain text. The Text to Columns feature parses these strings and converts them back into actual Excel date serial numbers.
Highlight the entire column or the specific cells containing the dates that are currently stored as text.
Navigate to the 'Data' tab on the Excel ribbon and click on the 'Text to Columns' button.
In step 1, choose 'Delimited' and click 'Next'. In step 2, clear all delimiter checkboxes and click 'Next'.
In step 3, under 'Column data format', select the 'Date' radio button. From the drop-down menu, choose the format that matches your current text (for example, 'MDY' for Month-Day-Year). Click 'Finish'.
Now that the values are real dates, right-click the cells, select 'Format Cells', go to the 'Custom' category, and enter your desired format (e.g., 'yyyy/m/d' or the ISO format 'yyyy-mm-dd').
Use a Formula to Extract and Convert Date Values
If Text to Columns disrupts your spreadsheet layout, you can use a combination of text functions to construct a valid date in a helper column.
Format and Convert Dates Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive data tools, including the Text to Columns wizard and extensive custom formatting, allowing you to clean and format date data seamlessly.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the document containing the incorrect text dates.
- 2. Access Text to Columns: Select the problematic column, go to the 'Data' tab, and click on 'Text to Columns'.
- 3. Convert to Date: Proceed to the final step of the wizard, set the format to 'Date' matching your source data, and click 'Finish'.
- 4. Customize Appearance: Right-click the converted cells, choose 'Format Cells', and apply your preferred custom date layout.
Frequently Asked Questions
Why doesn't changing the date format work in Excel?
When changing the format in the 'Format Cells' dialog has no effect, it usually means Excel is reading the data as a text string instead of a numerical date serial number. You must first convert the text to a date using tools like Text to Columns.
How do I apply the ISO date format (yyyy-mm-dd) to my spreadsheet?
Once your data is converted to real dates, right-click the selected cells and choose 'Format Cells'. Go to the 'Custom' category on the left pane, and type 'yyyy-mm-dd' into the Type input box, then click OK.
Can I use the DATEVALUE function to convert text to dates?
Yes, if your text dates are in a format that your computer's system regional settings can recognize natively, simply typing =DATEVALUE(A1) in an adjacent cell will convert the text to a valid date serial number, which you can then format.
