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

Watch on YouTube and give it a thumbs up.
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! 🙂

61 comments

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

  • The telco companies provide call logs with the date field using UTC or AEST to denote the date/time reference in the date field cell, across multiple time zones. This means i have to remove this to get excel to recognise it as a ‘date’ format. Be great to have a macro to adjust this to the local time zone and make it date/time format for other processing.

  • Hi Jon
    Thank you for this excellent trick.
    To address the non-US date format, I used the following formula:

    =(MID(A3,4,2)&”/”&LEFT(A3,2)&”/”&MID(A3,7,4))*1

    And it helped!
    Thanks once again.

  • Excel also hates written-out days of the week — “Thursday, July 11, 2019” for example, will never be recognized as a date, even if it EXACTLY matches the “long version” of dates in the date styles. You first have to delete the day. Then it will recognize it.

    I have to fix dates like that numerous times daily. So I have a macro that deletes all days of the week for my specified column.

  • In reading through the comments, I see there are several ways to convert text dates to real dates, but many are multi-step approaches: A helper column with text manipulation formulas, cell helpers to multiply, VBA, or some other method. Yours is the cleanest solution I have seen and had not known about.
    Thanks for the tip!

  • Nice. My favourite method (to convert any number stored as text, including dates), is using paste special to multiply the cells by 1. Like this:
    Type a number 1 (one) in any blank cell
    Copy the cell with the 1 in it
    Select the range if cells that should be numbers, but aren’t
    Bring up the Paste Special dialog box (I use CTRL+ALT+v)
    From the Paste Special dialog box, select Values and Multiply
    πŸ™‚

  • Hi I Often ran in the same problem with exported data from a PLC logging. It is even worse because it is timestamped. Both date and timestamp are often in US regional mode depending on the manufacturer of the PLC, I’m using European style dd/mm/yy 13h45.
    In the past i used techniques like TRIM LEFT RIGHT etc to get it right or start from the csv export and split it into columns.
    However Power Query is really the most appropriate tool to do this conversion, especially if you press the “text symbol” (or date, decimal,…) left of the column name; it offers a wide range of conversions including the most brilliant one “using locale”… This choice is not in the top menu so be aware!
    Using Power Query removes the need for VBA code, or repeating manual steps life FIND/REPLACE etc. Just drop a new file in the dedicated folder and it runs very smooth. I make these import methods for colleagues and they find it very user friendly!

  • I am afraid to inform you that this trick is not working for all the cells instead it is working for few cells only. I mean i have selected all the cells i.e. A3 to A65 and did Cnrl+H…

    But only few calls such as A3, A35 to A51, A55 to A59 & A62 are only getting chnaged as dates but others are not.

    Kinldy look into it and revert.

    • Hi Ashish,
      As Pieter mentioned in a comment below, this is likely due to your local date format being set to a different format. The example file contains dates in the MM/DD/YYYY format. The date and time format of your operating system would need to match this.

      This is the default date format in the U.S. If you are in a different country where the default is DD/MM/YYYY then this trick will not work. You could use an alternate method like Text to Columns or Power Query.

      However, the data you are exporting might be in your local format, and in that case the Find and Replace method would work. I’ll add a note about this to the article.

      I hope that helps. Thanks again! πŸ™‚

      • Jon: I don’t understand why the same technique does not work for non-US date formats. Surely Excel is just evaluating the input – as you indicated in your video – so what does it matter if the format is slightly different, so long as the input contains a forward slash?

  • Thank you for the new info

    Many times I came to the problems of finding text data in the shape of date which gave me incorrect information when I linked such data to formulas e.g. countifs or when I used advanced filter.
    In such cases I use different method which can be applicable with different dates writing like shown below.

    Date
    04‐07‐2019
    05.07.2019
    04‐07‐2019
    07.07.2019
    04‐07‐2019
    09/07/2019
    10‐07‐2019

    If we want to change the text to dates by using replace method, we need to do it in 3 stages. 1 replace for each separator so that we can cover all the data.

    There is another way to convert text dates to date format in one go which is by using β€œText to Column” tool within Data tap. And I used this way many times.

    First select the data
    Then select β€œText to Column” in Data tap
    Then select NEXT
    New window will appear then select NEXT again
    Now select Date, but make sure to select the right format or date, month and year similar to the arrangement in the text.
    Then select β€œFinish”
    Next, open the filter and you can see the data became in date format and grouped accordingly

    For better explanation, I prepared a blog in pdf format which downloadable following link below.

    https://drive.google.com/file/d/1uqd8kVEsgCrMCgYvkdkpDj7gUanPhyUP/view

    Thank
    Maki S. Hussain, PMP
    Baghdad, Iraq

    • Thanks so much Maki! Text to Columns is a great alternate solution, especially when your local date format is different from the source file OR you have different date formats in the column. I appreciate you creating the post with instructions. I’ll be doing a follow-up post & video with this technique.

      Thanks again! πŸ™‚

  • Great trick, BUT, you need to ensure that the date format in the spreadsheet matches what is set in your preferences. In my case: Australia uses DD/MM/YYYY.

      • Hi Jon
        Thank you for this excellent trick.
        To address the non-US date format, I used the following formula:

        =(MID(A3,4,2)&”/”&LEFT(A3,2)&”/”&MID(A3,7,4))*1

        And it helped!
        Thanks once again.

  • Nice trick didn’t know till now πŸ˜€

    when facing this kind of problem i use (Alt+A+E)…ENTER…Enter..Enter

    most of the times works, without telling that format of date we want.

    Thanks for sharing

  • Another method I’ve only recently started using is Data / Text to Columns (under Data Tools). The intended use is to split text into columns, but it can also convert text dates into true dates. It’s also a good way to convert numeric text to true numbers more easily than what I used to do (Paste Special Multiply by 1).

  • Select the cells you want to check and run this macro:

    Sub Chg_Text_to_Date()
    Dim cell As range
    For Each cell In Selection
    If IsDate(cell) Then
    cell.Value = DateValue(Trim(cell.Value))
    Selection.NumberFormat = “m/d/yyyy”
    End If
    Next
    End Sub

  • Copy any blank/empty cell. It must be a truly empty cell, not one that is pretending to be empty by using “” as its formula output. Then select the desired range of offending dates — even entire column(s) — and use Paste Special (Ctrl+Alt+V) with the Values and Add choices.

    This will force Excel to do “math” (adding zero) to all of the values. And as you might know, when Excel attempts math on an otherwise text formatted value, a numeric conversion happens whenever the value is recognizable as a numeric (or date) format. Any cells that contain true text or anything else that can’t be converted to a number are left alone, and any blank cells remain blank — i.e. you won’t end up with a zero in all the cells that were previously blank.

    The real beauty is that this will even work for text like Wednesday, July 3, 2019 or $5,372.18 or other recognizable date/number formats that might not be as easily handled by a find/replace trick.

  • Find & Replace doesn’t work in the sample file provided. I’m not sure why it would, since removing the slashes from “06/13/2019” merely results in “6132019”, which is still not a value that Excel can recognize as a date.

    A better solution in this example is to simply use the VALUE function. So in the sample file provided, you would add the formula “=VALUE(A3)” to cell G3 and copy it down. This will convert the dates to “General” format, and then the whole column can be converted to any date format you like.

    • Hi PG,
      It sounds like you didn’t add the slash character to the Replace With field. The slash needs to be in BOTH fields in the Find and Replace window. I hope that helps.

  • The find/replace method does not work for me with your sample expenses file. Office365 Home.
    The number formatting drop-down changes from General to Date, but they’re still not actually dates. All just text, no grouping and no difference between any of the previews.

    I have to use this method:
    Data | Text to Columns | Delimited | Uncheck all delimiter boxes | match the Date to suit (MDY in this case) | Finish

    • Hi Gary,
      This could be the case if you are outside the U.S. and your local date format is something other than MM/DD/YYYY. I’ll add a section to the article with more info on this issue. Thanks! πŸ™‚

  • Great help, explains an issue I’ve been having with a pivot table. However, if your Excel is not set up to use the US date format, then those dates that don’t make sense as a date in, say, the UK date format will not be forced to format as dates. For example, 05/10/19 could be either 5th October 2019 (UK) or 10th May 2019 (US) so this is forced to reformat as a date. However, 04/14/19, or 14th April 2019 in US format, is not a recognisable date in UK format, 4th Fourteen-ember 2019!! Consequently a mix of cells, some reformatted as dates, some still as general, is the result. It is important also to check the source data is using the expected date format.

    • Good point Simon! I’m going to add a section to the post that explains more about this potential issue. Hopefully the data you are exporting will be in your local date format, in which case this technique would work, but I realize that’s not always the case. Thanks again! πŸ™‚

  • I recently had problems converting text to date because spaces weren’t spaces and hyphens weren’t hyphens.

    That is to say, the spaces were “hard spaces”. They showed up when I copied the dates to Word.

  • Good post. I receive a monthly file with about 70K dates in a 20190702 layout, formatted as persistent text. I use:

    =mid(A2,5,2)&”/”&right(A2,2)&”/”&left(A2,4)

    Then use text-to-column (which is the only way I have figured out how to get the cells to quit reverting back to text), then copy and paste formula over the different columns needing conversion. Sounds lengthy, but really only takes a few minutes.

  • Wow Jon!
    I didn’t know this trick!!! All these years I would copy a blank cell and paste special and use add operation. Thanks for sharing this tip.

  • Great workaround! I typically select the column, type Ctrl+1 to invoke the Format Cells dialog, click the Number tab, in the Category list box select Date, then click the OK button.

    • Thanks Jimmy! In this case the dates are stored as text, and changing the number format will NOT change the values to a date data type in Excel. We have to use another workaround like the Find and Replace trick or one of the other solutions mentioned in the comments here.

  • We have the opposite problem: when excel is being too smart for its own good, and changes numbers that look like dates – but aren’t – into date formats.
    We frequently pull data out of our inventory management system. Things like “10-49”, which signify a quantity range, are always changed by excel to Oct-49 (Oct 1st, 1949)! Changing the cell format to “text” or “general” then reverts the cell value to the date code ie. 18172. The actual text “10-49” is completely lost – you have to type ’10-49 into the cell to force excel to see the value as text. With a large data set, using find/replace works by searching the date (as long as you get the date format right!) and replacing-all with ‘text.
    Often there’s just so many of them… 1-9 (1-Sep), 10-99 (Oct-99)… you get the picture. Interestingly things like 50-99 arent affected as excel cannot make a date from that.
    Is there another way? Can the “auto-date” format be tamed?!!

    • Great question Jeremy! Yes, there are probably more than a few ways to go about it. One way is to use Power Query to import the data into Excel.

      You can change the data type for each column before bringing it into Excel. In this case you would change that column to Text. Power Query will usually add a “Changed Type” step which automatically detects data types. You can either delete that step, or add an additional step to change the data type.

      I’ll add this one to my list for future posts. I think it will make a great example of using Power Query.

  • In a helper column one could use =date cell*1; or =DATEVALUE(Date Cell) – and copy paste by value over the range
    or select range and select macro or hot button

    Sub ConvertTextDateFormat()
    Dim rng As Range
    For Each rng In Selection
    rng.Value = DateValue(rng)
    Next rng
    End Sub

  • My solution is to select the column, then go to data and select text to columns. I don’t alter anything at that point, I just select finish. Magically the text will become a date. this works as well or better for numbers stored as text. Rather than selecting a range and looking for the little hidden menu that pops up asking if I want to convert the text into numbers, and then waiting an eternity for it to actually process the entire range, this makes the change almost instantaneously.

  • Hi Jon,

    Thanks for the hints. If the grouping of dates in a pivot table doesn’t work, something’s wrong with the raw date. Which means you’re a step ahead obviously, but sometimes it happens, that I realize I need to get back, and change the “wannabe” dates to real dates.

    Greg

  • I like to use Power Query. Make your data a table, and then Get Data, From Table (Excel 2016). Then once the query is opened, click on the column with the dates where the text icon is and change it to dates.

  • Another way to ‘fix’ it is to put a 1 into an empty cell then copy it and select the date range and paste special with multiply selected. Format date as desired.

    PC Keyboard Shortcuts:
    Copy – ctrl+c
    Select range – ctrl+shift+ arrow key in desired direction
    Paste special/Multiply – alt+e+s+m
    Format cells – ctrl+1

    Sorry, I’m not familiar w/ribbon lettering and/or locations. I use keyboard shortcuts a lot and they tend to be pre-ribbon ones.

  • I use the following macro to remove excess spaces in date fields:

    Sub TrimCells()
    ‘ Removes excess spaces from selected cells
    Dim rng As Range, cell As Range
    Set rng = Selection
    For Each cell In rng
    If IsEmpty(cell.Value) Then GoTo Skip
    If Not cell.HasFormula And (cell.Value WorksheetFunction.Trim(cell.Value)) Then
    cell.Value = WorksheetFunction.Trim(cell.Value)
    End If
    Skip:
    Next cell
    End Sub

  • Often times Excel does not recognize dates because of leading and trailing spaces. The solution is to run find and replace to remove all spaces. The trim function can also be used in a macro.

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