How to Convert Text to Dates with Find & Replace

How to Convert Text to Dates with Find & Replace

Bottom Line: Use the Find & Replace feature in Excel to quickly convert text to dates.

Skill Level: Beginner

Video Tutorial



YouTube Subscribe Logo Excel Campus



Download the Excel File

You’re welcome to use the same expense sheet that I’ve used as an example in the video. You can download it here:

Expenses.xlsx (19.5 KB)

Converting Text to Date Format

You might find that when you export data from online and financial programs such as QuickBooks Online, cells that are formatted as a date don’t transfer over with the same format. They may look like dates, but they their data type is actually text.

Dates are actually Text

I’d like to show you a quick tip to quickly convert all of those text cells to dates.

How to Tell If Your Dates Are Actually Text

A couple of quick ways to tell if the data type for your cell is text or date include taking a look at either the number format drop-down menu or the filter drop-down menu.

Number Format Drop-Down

If you go to the Number Format drop-down menu on the Home tab, you will see how the formatting would look for each of the categories in the drop-down. When the data is text, you can see that the Number, Short Date, and Long Date options all look the same because Excel is not recognizing the data as a date.

Number format drop-down text

If the cells were in date format, they would look like this instead:

Number format drop-down date

Filter Drop-Down

Another way to tell if a column of data is in text format is to look at the filter drop-down. If the entries aren’t being grouped by Excel into months and years, Excel is not recognizing them as dates.

Filter drop-down text

When formatted as dates, you can see the month and year groupings like this:

Filter drop-down date

Use Find & Replace to Evaluate Cells

A quick and easy way for Excel to evaluate these cells and to recognize them as dates is to use the Find & Replace feature. On the Home tab of the Ribbon, click on the Find & Select menu and choose Replace….

Find and Replace

This brings up the Find and Replace window. (The keyboard shortcut to bring up this window is Ctrl + H.)

If your dates are formatted with forward slashes (/), you are going to enter a forward slash into BOTH the Find what and Replace with fields. If your dates are formatted with dashes (-), then use dashes.

Then click Replace All. (The keyboard shortcut for Replace All is Alt + A.)

Convert Text to Dates with Find and Replace in Excel

By replacing these symbols, you are essentially forcing Excel to take a look at each of the cells you’ve selected, so that it can recognize their contents as dates.

Keyboard Shortcuts

Here are the keyboard shortcuts to open the Find & Replace Window and Replace All. Alt + i is the shortcut for Find All on the Find window, which is another one I use frequently.

Keyboard Shortcuts Find and Replace All

What If My Default Date Format is Different?

The Find and Replace technique will NOT work if your regional date format is different from the date format that the data is in.

In this example the date uses the following format: mm/dd/yyyy

I am in the U.S. and this is the same as the regional date format we use. The regional date format is set in the operating system (Windows or MacOS), NOT in Excel.

If you are in a different country and your regional date format is dd/mm/yyyy or something different, then this technique will not work.

Workarounds

There are still many ways to convert the text to dates.

You can change the regional date settings in your operating system to match the date format in the data. I wouldn’t recommend this unless you are only dealing with data from another country and constantly running into this issue.

Another option is to use Text to Columns or one of the other suggestions made in the comments section below. I will do a follow-up post on some of those solutions.

*A big thanks to Pieter, Simon, and Jonathan for pointing out this issue in the comments. We appreciate your support!

Bonus Tip

If you are exporting from an online or financial system, see if there is an option to export your data in a .CSV file. If so, it can help to avoid this problem of exporting dates as text.

Conclusion

So, if you find that your filter drop-down menu isn’t grouping by month and year, or date specific formulas are not calculating correctly, you should check your data to make sure Excel is reading it as a date data type. If not, a quick run through the Find & Replace feature should set things straight.

What other ways would you go about fixing this problem? Please leave a comment below with suggestions on other techniques you would use.

Thank you! 🙂

Leave a Reply

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