How to Convert Text to Dates [Data Cleanup]

Posted on March 23rd, 2010 in Excel Howtos , Learn Excel - 39 comments

How to Convert Text to Dates

Sometimes when we import data from another source in to excel, the dates are not imported properly. This can be due to any number of reasons, including,

  • The date format is different from one that is understood by excel
  • The data has some extra spaces, other characters before and after the date values.
  • The dates are formatted for another country (or date system) and hence your version of Excel wont recognize them.
  • The separator between date, month and year is not a known separator (for eg. 12=DEC=2009 instead of 12-Dec-2009), etc.

In this post, we will learn some tricks and ideas you can use to quickly convert text to dates.

Technique 1: Use Text to Columns Utility

  1. First copy the source data and paste it in a text file (open Notepad and paste there).
  2. Now copy the values from text file and paste them in Excel.
  3. At this point, Excel will prompt you for using “Text to columns” utility (or Text Import Wizard as it is called in Excel 2007)
    Text to columns utility (Text Import Wizard) Prompt
  4. Go to the Text Import Wizard (or Text to Columns dialog).
  5. Leave defaults or make changes in step 1 & 2.
  6. In step 3, select “Date” and specify the format of the date – like YMD, MDY, DMY, YDM, MYD or DYM. It doesn’t matter what is the format of source date, month or year is. Excel can smartly understand them.
    Text to date using Text Import Wizard in Excel
  7. Click “Finish”.

That is all. Your text dates are now converted to excel understandable dates.

Technique 2: Using Formulas to Convert Text to Dates

  1. Paste the data in a column (say “A”)
  2. Now depending on the format of source data, write one of the below formulas to convert text to dates.

Using DATEVALUE formula

DATEVALUE formula tells excel to fetch the date from a given input. It is a smart formula capable of converting dates stored as text to excel understandable date format. To convert a text in cell A1 to date, you just write =DATEVALUE(A1)

However, DATEVALUE formula has some limitations. It cannot process all types of dates. For eg. I have shown a few sample dates along with corresponding DATEVALUE output.

Using DATEVALUE formula to convert Text to Date

Readjusting Date Text so that it works with DATEVALUE formula

Whenever possible, your next best option is to re-adjust the source data text so that it can be understood by DATEVALUE formula. Here is an example.

Using Text formulas and DATEVALUE to convert Text to Date

We can use the text formulas like LEFT, RIGHT and MID to extract portions of the date text and then regroup them using & operator to create meaningful date text format that would be understood by DATEVALUE formula.

Technique 3: Using DATE formula to Convert Text to Dates

If your data has separate columns for date, month and year, you can use DATE formula to convert the data to dates like this:

=DATE(year,month,day)

For eg. =DATE(2009,12,31) will give the date 31st December, 2009.

Bonus Technique: Converting Dates to Text

If you want to convert excel dates to text values (for your report or some other purpose), you can use the TEXT formula like this:

=TEXT(A1,"DD-MMM-YYYY") will convert date in Cell A1 to DD-MMM-YYYY format. You can pass any other date / time formats to TEXT formula as well. [more: tutorials on TEXT() formula]

How do you deal with troublesome dates?

Of course, if it is a real date, we can always bolt. But if it is a date in the data, we need some tools to deal with it. I used to rely on formula based methods to clean the dates. But recently I discovered the import-text date conversion method. This is very powerful and straightforward. Now, I use it whenever possible to clean up my date data.

What about you? How do you deal with buggy / faulty dates in Excel?

Recommended Articles:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

39 Responses to “How to Convert Text to Dates [Data Cleanup]”

  1. Gerald Higgins says:

    Some good ideas. I had to do something like this recently, I can’t remember the exact format of the input data, but it was something like “01.01.2010″ and using Edit, Find and Replace, to find “.” and replace with “/” was enough. I’m not suggesting this is better than any of the methods already listed.

  2. Martin says:

    Great Post !!

    the DATE function is great for all of us not using MMDDYY format, because we can manipulate the string containing the date to proper present it, when is not in the format we want to.

    also, when importing text files, sometimes I’ve found the need to create an auxiliary list for the months, and also adding the 2 first digits to the year, before even pass the result as argument to the DATE function.

    Rgds.

  3. Mark K says:

    I created a macro with a hotkey for this. Our database stores dates in ’4/1/2002 12:00:00 AM’ format which shows up in Excel as ’00:00.0′. Here is the macro:

    Sub format_date()

    ‘ format_date Macro
    ‘ format selection to date

    ‘ Keyboard Shortcut: Ctrl+d

    Selection.NumberFormat = “m/d/yyyy”
    End Sub

  4. Yogesh Gupta says:

    Hi Chandoo

    Text to column dialog box can be triggered from “Text to Columns” button on Data Tab. With this you can easily avoid the steps to copy source data to note pad file. Just select the data cells on your excel file and start directly from step 4 in Technique 1 list above using Text to Column button on Data Tab.

    Thanks
    Yogesh Gupta

  5. Pankaj Verma says:

    Hi Chandoo,

    I am glad you published this post. I have been using the “text to column” tool for quite some time. It really comes in handy in cleaning up the data and even sometimes for creating the data for uploads (for example, for testing). This tool is very useful when you want to change the data that excel understands to the data what your software understands. In the process of changing the data a combination of text to column, concatenate formulae etc can be used and bingo!!! you get the data ready to uploaded in system (most of the time the coders leave this flabbergasted – “Man, how did you do that?? that was quite a DATA, boy”)……;)
    Another example of the use of text to column is to trifurcate a name in three columns namely First Name, Middle Name and Last Name or Surname.

    Cheers!!!!

    Pankaj Verma

  6. Chandoo says:

    @Gerald.. good input on using find / replace. Thanks for sharing it. :)

    @Martin: I guess excel asks you for the date system (19xx or 20xx) when you import dates with 2 digit year codes.

    @Martin: Very good and very simple macro. Thanks for posting it for all of us.

    @Yogesh: Thanks for pointing that out. In fact, I had the text-to-columns on my quick access bar because my office comp uses european date system and all the incoming CSVs used US date system.

    @Pankaj: good use of text to columns to split names. I use it often to clean up urls, email ids and other stuff too.

  7. Rick Rothstein (MVP - Excel) says:

    For those who might be interested in a VB solution, below is a macro that will take a column of text strings that are meant to be dates and convert them into dates. The macro will convert the following patterned text into real dates as indicated (“y” is a year digit, “m” is a month digit or letter depending on if there are 1, 2 or 3 of them in the pattern, and “d” is a day digit)…

    yy (January 1st assumed)
    m/d or m/d/ (current year assumed)
    m/dd or m/dd/ (current year assumed)
    mm/d or mm/d/ (current year assumed)
    mm/dd or mm/dd (current year assumed)
    mm/dd/yy
    mm/dd/yyyy
    mmdd (current year assumed)
    mmddyy
    mmddyyyy
    dmmmyyyy or ddmmmyyyy
    yyyymmmdd or yyyymmmd

    Note that the date order is month/day. I **think** the code will work if your system uses day/month ordering; but, since I have never done any international programming, I don’t know that for sure. I would appreciate feedback from those whose date order is day/month. Okay, anyway, here is the macro (set the two Const statement assignments to match your actual setup)…

    Sub ConvertToProperDates()
    Dim D As Date, S As String, Sin As String, X As Long, LastRow As Long
    Const StartRow As Long = 2
    Const DateCol As String = “A”
    LastRow = Cells(Rows.Count, DateCol).End(xlUp).Row
    On Error Resume Next
    For X = StartRow To LastRow
    Sin = Cells(X, DateCol).Value
    S = Trim(Replace(Replace(Sin, “/”, “-”), Application. _
    International(xlDateSeparator), “-”))
    If Right(S, 1) = “-” Then S = Left(S, Len(S) – 1)
    If Len(S) = 2 Then
    D = DateSerial(S, 1, 1)
    ElseIf InStr(S, “-”) Then
    D = CDate(S)
    ElseIf S Like “#[A-Za-z][A-Za-z][A-Za-z]####” Or _
    S Like “##[A-Za-z][A-Za-z][A-Za-z]####” Then
    D = CDate(Val(S) & “-” & Mid(S, Len(S) – 6, 3) & “-” & Right(S, 4))
    ElseIf S Like “####[A-Za-z][A-Za-z][A-Za-z]#” Or _
    S Like “####[A-Za-z][A-Za-z][A-Za-z]##” Then
    D = CDate(Left(S, 4) & “-” & Mid(S, 5, 3) & “-” & Mid(S, 8))
    Else
    S = Format(S, “!&&-&&-&&&&”)
    If Right(S, 1) = “-” Then S = Left(S, Len(S) – 1)
    D = CDate(S)
    End If
    If Err.Number Then
    Cells(X, DateCol).Value = “?? ” & Sin & ” ??”
    Err.Clear
    Else
    Cells(X, DateCol).Value = D
    End If
    Next
    End Sub

  8. Rick Rothstein (MVP - Excel) says:

    Just a follow up on my macro… I notice that code got posted with some character substitutions that you will need to correct in order to make the code work in your VB code window… after you copy/paste the above code into your VB code window in Excel, change the beginning quote marks (“) and ending quotemarks (”) to normal quote marks; and in the three statements containing the Len(S) function calls, change the elongated minus signs that were placed after them to normal minus signs. Everything else looks like is copy/pastes just fine.

    • Suril Mehta says:

      Rick this is giving an error: ‘Type mismatch’ for LastRow = Cells(Rows.Count, DateCol).End(xlUp).Row

  9. hwsris says:

    Text to column is very great menu to convert data type.

  10. Robapottamus says:

    Hi

    Thanks for the tips Chandoo.

    Unfortunately I have an issue that does not appear to be addressed in various blogs that i have come across yet. I am pulling dates into Excel as text, looking like this: 010805, which ought to read as 01/08/2005 (dd/mm/yyyy). However, using technique 3 as described by Chandoo above, it converts to 10/08/2005 so it is ignoring the first “0″ in my text.
    I have also tried using a custom format date to dd/mm/yyyy, but unfortunately this option returns a date of the text as 31/07/1929.

    Any suggestions to get around this issue?

    Thanks
    Robapottamus

    • Rudra sharma says:

      first of all format the cells where your date gets imported to as text – formatcell-tex
      import your date(without separator)
      assuming your imported date is in A1
      enter formula
      =(LEFT(A1,2)&”/”&MID(A1,3,2)&”/”&RIGHT(A1,2))+0
      0 is added to bring it to number format

  11. Hui... says:

    @Robapottamus
    Add a helper column
    =DATE(2000+RIGHT(A2,2),MID(A2,3,2),LEFT(A2,2))
    Copy down
    Copy and paste as values
    Format as Dates with dd/mm/yyyy

  12. Kenny says:

    Desperate for help!
    I work with importing data into excel often from different tables within our system. The problem is that different tables have different formats for storing a date field. What I have not seen addressed is what happens when date fields are stored as NUMERIC data types instead of text?
    For example,
    Table_1 (The easy table): Field name [Customer_Add_Date] formatted as CCYYMMDD stored as a numeric field type. So to convert this to a date, I have to use a date conversion =DATE(LEFT([Customer_Add_Date],4),MID([Customer_Add_Date],5,2),RIGHT([Customer_Add_Date],2))
    Table_2 (The painful table): This table stores dates in a numeric field type as YYMMDD. So if there was a payment transaction on January 9, 2000, the data would look like this “109″. So I have to qualify using lengths and it is a painful experience.
    Has anyone run across this before? Does anyone have a user-defined function that can convert numeric field types into dates??
    Thanks!!!

  13. Kaleem says:

    I use CNTRL+H and replce all “- and /” with Blanks and then use Alt+D+T convert it to Date Format

  14. Ritesh says:

    Can you please tell me How to Convert dates to text.
    Like some one enter 3/1/2012 , it should look 3 jan.
    In Excel 2010.

    Thanks
    Ritesh

    • Hui... says:

      @Ritesh
      Select the cell
      Ctrl 1
      On the Number Tab, select Custom
      in the Type: Box enter
      d mmm
      Apply
      Have a read of http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

  15. Ashok says:

    Dear All,
    Need your help plz..

    I have imported some data from text to excel and got a text format in the cell as “LW117541      20/04/2011″ and another cell with “LW118979      08/07/2011″ then I wrote a formula to get the date separately as “=RIGHT(G10,10)” and separated the date and now i couldnt convert to any of the date format. though my cell value is “21/04/2011″ i couldnt understand in what format it is? then based on some of the above comments I just replaced “/” with “-” and found to format again and only the second date as mentioned above has changed “08/07/2011″ but the first date “20/04/2011″ seems to have the old format.
    Kindly help!! 

  16. Jignesh Pandya says:

    Hi,
    my machine gives date in the format 19.06.2012 and I simply use “substitute” formula to make it understand to excel and also calculate the differance in dates
    Thanks
    Jignesh Pandya

  17. stan says:

    you just save me from tedious work of converting text day to excel date.
    Thank you for your generosity.
     
     

  18. emilia says:

    Hi, I am receiving the automated excel reports generated from online tool. The date formats are shown in a following way:May 14 2012. I wanted to make a calculation of the future dates – e.g. review in 365 days from given date but it shows the VALUE# error. I’ve tried to check the date format using the text import wizard but it just doesn’t work – it doesn’t change a date format, it still shows me the error in the formula :/

  19. Dave Billington says:

    Hi I receive a file with dates in the following format and I spend a lot of time converting them into a useable format; can anyone suggest an easy to understand / implement solution, please….
     

    June 15, 2012 9:59 AM

    May 24, 2012 12:15 PM

    April 12, 2012 10:26 AM

    March 23, 2012 8:49 AM

    I dont need / want the time element.  I simply want e.g. 15/06/2012
    Thanks.

  20. [...] Buried inside heap of features in Excel is this beautiful Text to columns utility, that can take any text and convert it in to many columns based on the delimiter you specify. [more uses of text to columns] [...]

  21. Fil says:

    Just want to say THANK YOU! Really appreciate the pedagogical approach on this site. The chart showing the example of date formats and what they’ll retreive via the DateValue formula let me to a solution in no time. THANKS AGAIN!

  22. Rajan says:

    Your Technique 1 saved me Hours and Hours of painstaking task. Thank you Chandoo.

  23. Francis Owiredu says:

    All i can say is BRILLIANT!! Technique 1 worked well for me. Many Thanks and much appreciated

    • Surendra says:

      4/29/2013 6:44:00 AM

      The above date and time is in general format .need to covert to date and time format(yyyy-mm-dd hh:MM:SS).Tried with text formaula but not giving any answer.

  24. Jai says:

    great work!! Saved lot of my time.

  25. G C says:

    Thank you so much

  26. Shrey says:

    Hi,
    I get Dates in a software generated report as “Thu 4/18/2013 2:15 PM” in text format.
    I use Date(mid(search(“/”,… to extract the date but it is problematic because / is placed differently in dates like 4/8/2013, 4/18/2013 and 11/18/2012.

    Is there some simple way to extract dates from such a text format?

  27. Mark says:

    Thanks. I was able to use mid and right to reformat the dates!

  28. Joe says:

    I need to create a macro that changes the date on the current formula in about 30 Cells to the current date -1 (Previous day)
    The formula looks like this
    =+’Y:\2014\Shifts\July 2013\[18 July 2013.xls]Shifts’!$D$12

    The 18 July 2013 needs to change when the macro runs to the current date – 1

    Your help would be appreciated.

  29. Date format says:

    [...] and welcome to MrExcel. Take a look here and see if Chandoo has the solution you require…. How to Convert Text to Dates [Data Cleanup] | Chandoo.org – Learn Microsoft Excel Online I hope that helps. [...]

  30. Date Format says:

    [...] check out Risk's suggestion in- Format date from Text to Date also try How to Convert Text to Dates [Data Cleanup] | Chandoo.org – Learn Microsoft Excel Online FarmerScott [...]

  31. […] Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix. […]

  32. […] Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix. […]

  33. Penelopy says:

    What about a date starting from a Century like 1131122 (22nd Nov 2013).

    this is what I use at work, but I completely DO NOT understand why it works. Anybody would like to shed some light?

    =DATE(MOD(INT(A1/10000),100)+2000,MOD(INT(A1/100),100),MOD(A1,100))

    thanks in advance,
    Penelopy

Leave a Reply