Bottom Line: Change data that is formatted as text to appear in date format using the Text to Columns feature.
Skill Level: Beginner
Video Tutorial
Download the Excel File
Text That Looks Like Dates
In a previous tutorial, I explained how to convert text to dates in Excel using the Find & Replace feature. This is sometimes necessary when you have exported data from another program and Excel doesn't recognize the data type as a date.
If you are unsure whether or not Excel is reading your data as text or dates, you can learn how to be sure in that tutorial, titled How to Convert Text to Dates with Find & Replace. In short, the Number Format Drop-Down will not have any variation between formats if the data type is text.
Several users reported back on that article that the Find & Replace feature didn't work, and the reason is because their Microsoft settings were set to a different region/language. These users were located in regions where the normal format for dates places the day in front of the month (13/06/2019) instead of the month and then day (06/13/2019).
Using the Text to Columns Feature to Change the Format
An alternative method for changing the format is by using the Text to Columns feature. This feature works regardless of what region you live in. Let me show you how to use Text to Columns.
- Start by selecting all of the text that you want to convert to dates. To do this quickly, select your first cell of data at the top of a column and then hit Ctrl + Shift + ↓. This will take you to the end of your contiguous column.
2. Next, open the Text to Columns Wizard by selecting the Text to Columns button on the Data tab of the Ribbon.
3. On the first step of the wizard, make sure the Delimited option is selected, and then click Next.
4. For Step 2, you do not need to make any changes. The Tab option will be selected already and you can leave it checked. Click Next.
5. For Step 3, you will want to select the Date button and then choose the format that the data currently shows. In other words, if the text is showing Month-Day-Year, chose MDY; if Day-Month-Year, choose DMY, and so forth. Then click Finish.
Now the data format has been changed from text to date. If the region selected on our computer settings is outside of the United States, the day will appear before the month as depicted below in the “After” column.
Of course, if you are in the United States, the before and after will look pretty similar, if not identical, but this Text to Columns method will still convert your text to a date format, so that it can be properly used for formulas, pivot tables, and so forth.
Bonus: How Dates Work in Excel
If you are curious how the date system works in Excel, and how the dates are stored as numbers, I've written a whole post about it here: How Dates Work in Excel – The Calendar System Explained.
Conclusion
So there you have it: another way to convert text to dates, especially if your date format is different because of the region where you live and work. If you are in the United States, you can choose between this method and the Find & Replace method mentioned before.
Let me know if you have any questions or comments below!
Nice overview. One wrinkle is that the default Excel date system assumes that 1900 was a leap year. It wasn’t. So 1/1/1901 is 367 not 366. Steve Jobs, in his inimitable style, pointed this out and so Excel on the Mac uses 1/1/1904 as day 1. If you format dates on a Mac, then move them over to a PC, your dates are off by nearly 4 years. The solution is on the Excel Options Advanced dialog: “Use 1904 Date System.” On a PC this is not checked. On a Mac, it is. If you will be going back and forth, make sure you are consistent in how this is checked.
i like these notices. Keep ’em coming.
Hi,
The function helped a lot, Thanks for depositing this piece to my wealth of Knowledge.
Hi
how can i do the same procedure with vba code?
how can i do the VBA macro in same step, but change it to fixed width?
This worked but not for all the cells in the row – any guess as to why?