How to Convert Text to Dates with Text to Columns

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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

If you'd like to practice using the same Excel file I used in the video, you can download it here.

Text To Dates - Text To Columns.xlsx (26.4 KB)

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.

Number format drop down 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.

  1. 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.
Select All of the Data in a Column

2. Next, open the Text to Columns Wizard by selecting the Text to Columns button on the Data tab of the Ribbon.

Text to Columns Button on Data Tab
Click to Enlarge

3. On the first step of the wizard, make sure the Delimited option is selected, and then click Next.

Step 1 of Text to Columns Wizard

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.

Step 2 of Text to Columns Wizard

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.

Step 3 of Text to Columns Wizard

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.

Comparison Before and After Text to Columns

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!

3 comments

Your email address will not be published. Required fields are marked *

  • 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.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly