Bottom Line: Use the Find & Replace feature in Excel to quickly convert text to dates.
Skill Level: Beginner
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.
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.
If the cells were in date format, they would look like this instead:
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.
When formatted as dates, you can see the month and year groupings like this:
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….
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.)
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.
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.
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.
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!
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.
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! 🙂