How to Convert Text Such as 06.08.2020 into Dates in Excel
Question details
The user needs to convert text strings that resemble dates formatted with periods (e.g., 06.08.2020) into actual recognizable date values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing data from web pages where dates contain periods and are incorrectly identified as plain text.
- Observed behavior
- Simply changing the cell format to 'Date' does not convert the text into date values, as Excel treats the numbers separated by periods as literal text strings.
Ensure all the text-based dates you want to convert are located in a single column, as the conversion tool processes one column of data at a time.
Use Text to Columns to Parse and Convert Dates
The Text to Columns feature can accurately parse custom text structures (like DMY with periods) and force Excel to recognize them as valid date serial numbers.
Because imported dates often contain non-standard separators like periods, Excel cannot natively read them as dates. The Text to Columns wizard bridges this gap by letting you explicitly define the original format of the text so Excel can translate it correctly.
Highlight the entire column containing the text dates you want to convert.
Navigate to the Data tab on the Excel ribbon and click on the 'Text to Columns' button.
Leave the data type as 'Delimited' and click 'Next' through the first two steps until you reach Step 3 of 3.
In the 'Column data format' section, select the 'Date' radio button. From the adjacent dropdown menu, choose the format that matches your text. For example, choose 'DMY' if your text is structured as Day-Month-Year.
Click 'Finish'. Excel will immediately convert the text strings into actual date values.
Convert Text to Dates Effortlessly in WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive Text to Columns utility that easily fixes imported web data, transforming stubborn text strings into perfectly formatted date values in seconds.
- 1. Select your column: Open your spreadsheet in WPS Office and highlight the column containing the text dates.
- 2. Launch Text to Columns: Go to the 'Data' tab and select 'Text to Columns' to open the conversion wizard.
- 3. Convert to Date: Click Next until Step 3, choose 'Date', select your format (such as DMY), and click 'Finish'.
Frequently Asked Questions
Why doesn't changing the cell format to 'Date' work on imported text?
When data is imported from the web, Excel often imports numbers containing periods as literal text strings. Changing the cell format only dictates how a valid number is displayed; it does not actively parse or recalculate a text string into an underlying date serial number.
How do I keep the periods in my dates after converting them?
Once the text is converted to a real date, Excel will apply your Windows system default date format (usually employing slashes or dashes). To restore the periods, select the converted cells, right-click and choose 'Format Cells', go to the 'Custom' category, and type 'dd.mm.yyyy' into the Type box.
What if Text to Columns converts my dates incorrectly to MDY instead of DMY?
If the conversion yields the wrong day and month alignment, it means the default logic misidentified the structure. Ensure you explicitly select 'DMY' from the Date dropdown menu in Step 3 of the Text to Columns wizard before clicking Finish.
