Homework: Can you extract dates from text?

Posted on August 17th, 2012 in Excel Challenges , Excel Howtos - 146 comments

So who is up for a challenge? Can you use only formulas and extract dates buried inside text?

Your mission, if you choose to accept it,

  1. Download this file.
  2. In column C, write a formula such that you can extract the date in column B
  3. If you succeed, post your solution here as a comment.
  4. If you fail, drink some coffee, start afresh.

Extract Dates from text using formulas

What are you waiting for? Start extracting!

Need some help?

If you want some clues, check out below links:

Want more challenges? We got’em too

You better have access to lots of coffee or something strong, cause it going to get tough.

PS: If you feel like cheating, here is a solution.

Your email address is safe with us. Our policies

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

146 Responses to “Homework: Can you extract dates from text?”

  1. milang says:

    Something like 
    =IFERROR( DATEVALUE( MID( B4 , FIND( “/” , B4 ) – 2  ,10 ) ) , “” ) 

  2. dane_andrew says:

    works with cells with 07/01/1981:

    =MID(B4,FIND(“/”,B4)-2,10)

  3. Eamon says:

    =MID(B4,MAX(1,FIND(“/”,B4)-3),11)
     

     

  4. Aiks says:

    This is the formula I used… it works assuming that the DOB will always be in MM/DD/YYYY format

    =IFERROR(DATEVALUE(MID(B4,FIND(“/”,B4)-2,2)&”/”&MID(B4,FIND(“/”,B4,1+FIND(“/”,B4))-2,2)&”/”&MID(B4,FIND(“/”,B4,1+FIND(“/”,B4))+1,4)),””)

  5. Ahmed says:

    the is the formula, starting from C4 then drag down

    =IFERROR(MID(B4,(FIND(“/”,B4)-2),10),”No DOB Found”)

    only drawback is that if a / is found without a date (if an entry in column B contained “/i love excel more than flying ninjas ” for an example) the formula will return rubbish 

    can be solved by double checking we have two / and the distance between them is 2, if so, formula would be:

    =IF(COUNTIF(B4,”*/**/*”)=0,”No DOB Found”,IFERROR(MID(B4,(FIND(“/”,B4)-2),10),”No DOB Found”)) 

    If there are two / in a cell in column B and the distance between them is 2 and yet not a date this is doable but now we are getting ridiculous…

    Regards,
    Ahmad 

  6. Anup Agarwal says:

    I think this works:

    =IFERROR(DATE(MID(B4,SEARCH(“/”,B4)+4,4),MID(B4,SEARCH(“/”,B4)-2,2),MID(B4,SEARCH(“/”,B4)+1,2)),”Gadheda!!”) 

  7. Faseeh says:

    I think the simplest is with MID() and FIND():
    `=IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),”..”)`
     
     
     

  8. Misca says:

    I’d use =IFERROR(DATEVALUE(MID(B4,FIND(“/”,B4)-2,10)),””)

  9. Fowmy says:

    =IFERROR(MID(B4,SEARCH(“/”,B4)-2,10),””)

  10. Chandoo says:

    Interesting approaches everyone. How would you write the formula if there can be a / without date too?

  11. Eduardo Quental says:

    em português ficou assim:

    =SEERRO(EXT.TEXTO(B9;LOCALIZAR(“/”;B9;1)-2;10);”No Answer”)

  12. Shadow Jam says:

    Well i don’t know quite how I would write it, but i’m sure there is an approach that would use an array and the code function. This would have the advantage that / & . & – and the numbers are all consecutive from 45 to 57.

    One could probably use this to isolate a date. I would be interested to see what someone with better skills than I could do with this approach. 

  13. Rogers says:

    If only this format is used, I’d go with:
    =IFERROR(DATEVALUE(MID($B5;SEARCH(“??/??/????”;$B5);10));””)

  14. Chris Byham says:

    I’ve tried to tackle in a different vein as below:

    {=MAX(IFERROR(DATEVALUE(MID(B4,ROW($A$1:$A$200),10)),0))}

    So, looking at each set of 10 characters (length of date in format mm/dd/yyyy) and trying to use the datevalue function to change the text into an actual date. Then removing the error values using the IFERROR.

    Finally, I use the MAX function to obtain the greatest date (using MIN returns the 1st of the month required, as datevalue returns a result for a mm/yyyy string).

    The only thing with this formula is when a date is not apparent – in these cases, it seems to return a 0 (and I haven’t quite worked out why yet) – I can remove this by using an IF(…..=0,””,…..), but wanted to find a cleaner way – any ideas?

    • Chris Byham says:

      ps.

      This formula should work with additional numbers or / in the text string – if there are two or more dates, it will obviously pick the biggest as using the MAX function. 

  15. Ben Niebuhr says:

    My entry:

    =IFERROR(
    DATEVALUE(MID(B4,MIN(FIND(“0″,B4),FIND(“1″,B4)),10)),
    “No date entered!”)

    I can’t believe I didn’t think to search for the “/”. And now I see Chandoo’s challenge about other numbers in the string, I think mine fails.

    Rogers gets my vote for best answer so far. I didn’t know about the search for patterns either. Awesome tip!

  16. Vikas Chauhan says:

    I hope this formula works for this table. The formula is as follows:
    =DATE(0+MID(B4,FIND(“/”,B4)+4,4),MID(B4,FIND(“/”,B4)+1,2),MID(B4,FIND(“/”,B4)-2,2))

    I hope this is useful. The output for this formula is: 

    When is your date of birth?
    DOB

    My date of birth is 07/01/1981
    1/7/1981

    Hmm, 07/01/1981, I think that is when I was born
    1/7/1981

    Oh wait, was it 07/01/1981? I think so
    1/7/1981

    07/01/1981 that is when I landed here.
    1/7/1981

    Date of birth?!? What is that now?
    #VALUE!

    Let me ask my wife, No wait, I think it is 07/01/1981
    1/7/1981

     

    • Vikas Chauhan says:

      This formula also works:
      =MID(B4,FIND(“/”,B4)-2,10)

      almost same as  Radhish Thekkute suggested.

    • Dolphin Godfred says:

      Vikas, your function looks great. Need a small tweak though.
      Try the date “01/14/1978″. I get the output “01-02-1979″.
      Check the function arguments (click the fx icon before the formula bar). It shows the DATE arguments Year, Month, and Date. It looks like you have given the Month formula in the Date and Date formula in the Month.
      Change them and it works like a charm.

      -DG

      • Vikas Chauhan says:

        Thanks Mr. Dolphin Godfred. You are correct.
        I appreciate your knowledge.

        This looks better:
        =DATE(0+MID(B4,FIND(“/”,B4)+4,4),MID(B4,FIND(“/”,B4)-2,2),MID(B4,FIND(“/”,B4)+1,2)) 

  17. Luis Hurtado says:

    I Try… work fine:
    Spanish version…

    =SI.ERROR(FECHANUMERO(EXTRAE(B4,HALLAR(“/”,B4,1)-2,10)),”No hay DOB”)

    Regards.

    Luis Fdo.
     

  18. Vanita says:

    =IF(ISERROR(MID(B4,FIND(“/”,B4)-2,10)),”No date”,DATEVALUE(MID(B4,FIND(“/”,B4)-2,10)))

  19. Radhish Thekkute says:

    =MID(B4,SEARCH(“/”,B4)-2,10)

  20. As simple as
    =RegExFind(B4;”\d\d/\d\d/\d\d\d\d”)
    !!!

    There is a wonderful add-on Regexp Search and Replace for Excel that can be downloaded from http://www.codedawn.com/excel-add-ins.php
    This add-on allows to use aadditional worksheet functions like the one above 
    as well as to search via Ctrl+F dialog using regex wildcards. 

  21. Eduardo says:

    Em portugues  – criei uma formula no gerenciador de nomes
    =SEERRO(PROCURAR(“/”;’extract-dates’!B4);0)

    em cada célula usei
    =SE(formula1;EXT.TEXTO(B4;formula1+1;2)&”/”&EXT.TEXTO(B4;formula1-2;2)&”/”&EXT.TEXTO(B4;formula1+4;4);””)

    abs
    Eduardo Chamon – Brasil

  22. Em gerenciador de nomes criei uma fórmula

    =SEERRO(PROCURAR(“/”;’extract-dates’!B4);0)

    e para cada célula usei

    =SE(formula1;EXT.TEXTO(B4;formula1+1;2)&”/”&EXT.TEXTO(B4;formula1-2;2)&”/”&EXT.TEXTO(B4;formula1+4;4);””)

  23. David says:

    =IF(ISERROR(MID(B4,SEARCH(“??/??/????”,B4,1),10)),”No Date”,MID(B4,SEARCH(“??/??/????”,B4,1),10))

  24. Andy says:

    Here is what I got:=IFERROR(MID(B4,FIND(“/”,B4)-2,10),”No Date”)

  25. josh says:

    I couldn’t help but think of this in terms of the “getNumber” public function that I learned on here not too long ago.  By putting this as a public function, I could reuse it at will and not have to remember the formula.  Unfortunately, I’m no VBA expert.  But I think someone one here could use this idea and modify the code into something better.

    Public Function getdate2(fromThis As Range) As String
        ‘Extract the date from a cell and return it.
        Dim retVal As String
        Dim ltr As String, i As Integer, datecheck As Boolean
       
        retVal = “”
        getdate2 = “”
        datecheck = False
       
        On Error GoTo last
        ‘Check if the range contains a date
        If fromThis.Value Like “*/*/*” Then
            datecheck = True
        ElseIf fromThis.Value Like “*-*-*” Then
            datecheck = True
        End If
       
        For i = 1 To Len(fromThis)
            ltr = Mid(fromThis, i, 1)
            If IsNumeric(ltr) Then
                retVal = retVal & ltr
            ElseIf ltr = “/” And datecheck Then
                retVal = retVal & ltr
            ElseIf ltr = “-” And datecheck Then
                retVal = retVal & “-”
            End If
        Next i
        getdate2 = Format(retVal, “mm/dd/yyyy”)
    last:
    End Function

  26. Esteban says:

    Same as the last post

    =SI.ERROR(FECHANUMERO(EXTRAE(B4;ENCONTRAR(“/”;B4;1)-2;10));””)

    Esteban

  27. cALi says:

    Similar but longer than Luis’s solution:
    =IFERROR(DATE(MID(B4,FIND(“/”,B4)+4,4),MID(B4,FIND(“/”,B4)+1,2),MID(B4,FIND(“/”,B4)-2,2)),”No Date!”)

    En español:
    =SI.ERROR(FECHA(EXTRAE(B4,ENCONTRAR(“/”,B4)+4,4),EXTRAE(B4,ENCONTRAR(“/”,B4)+1,2),EXTRAE(B4,ENCONTRAR(“/”,B4)-2,2)),”No hay fecha!”)
     

  28. Eduardo Raupp Schardosim says:

    =DIREITA(ESQUERDA(B4;LOCALIZAR(“/”;B4)+7);10)

  29. Fred C says:

    =IF(ISERROR(DATE(MID(B9,FIND(“/”,B9)+4,4),MID(B9,FIND(“/”,B9)-2,2),MID(B9,FIND(“/”,B9)+1,2))),””,DATE(MID(B9,FIND(“/”,B9)+4,4),MID(B9,FIND(“/”,B9)-2,2),MID(B9,FIND(“/”,B9)+1,2)))

  30. Unal says:

    =IFERROR(MID(B4,SEARCH(“0″,B4,1),10),”no date in here :)”)

  31. Shrivallabha says:

    A] Following entered as ARRAY FORMULA will work:
    =MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4,1),LEN(B4)+1)),10)
    will give erroneous results in case of non-date numbers and will leave blank if there is no number.

    B] This non-array formula should have worked (cell formatted as date) but it fails when a character other than space is placed next to it.
    =LOOKUP(9.99E+307,TRIM(MID(SUBSTITUTE(B4,” “,REPT(” “,100)),(ROW($A$1:$A$20)-1)*100+1,100))+0)

  32. Bryan says:

    I would do something like this:  {=MID(B3,MATCH(TRUE,ISNUMBER(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1)),0),COUNT(–MID(B3,ROW(INDIRECT(“$1:$”&LEN(B3))),1))+2)}.
     
    This would allow for different formats of the date and different seperators like / – ,.

          

  33. Isti says:

    As i m going through the given file my simplest formula is as below.

    =MID(B4,FIND(“/”,B4)-2,10)

    not think more.

    may be there should be more solution.

    Take a cup of coffee guyssssss..

    regards
    isti 

  34. SALVADOR says:

    =IFERROR(MID(B8,SEARCH(“/”,B8,1)-2,10),”NA”)

  35. Jeet Singh says:

    =IFERROR(MID(B4,MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;”/”},B4),””)),10),””)

  36. Michael Royalty says:

    I learned about the Mid function from this homework, and also a better use of wildcards with the search function!
    The original formula I used was this: =IFERROR(IF(SEARCH(“*/*/*”,B4),LEFT(RIGHT(B4,LEN(B4)-SEARCH(“/”,B4)+3),10),0),”No Date Found”)
    I’ve simplified it to this: =IFERROR(DATEVALUE(MID(B4,SEARCH(“??/??/????”,B4),10)),”No Date Found”)

  37. Jitendra says:

    Here is the formula,

     =IF(ISERROR(FIND(“/”,B4,1)),””,MID(B4,(FIND(“/”,B4,1)-2),2)&”/”&MID(B4,(FIND(“/”,B4,1)+1),2)&MID(B4,(FIND(“/”,B4,1)+3),5))

  38. Brian says:

    =IF(ISERROR(MID(B9,SEARCH(“0″,B9,1),10)),” “,MID(B9,SEARCH(“0″,B9,1),10))

  39. Ramnath Divakaran says:

    =IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),”No Date Found”)

  40. Don Hopkins says:

    =IF(ISERR(SEARCH(“??/??/????”,B4)),”No valid date format found”,MID(B4,SEARCH(“??/??/????”,B4),10))

  41. Lewis Kirby says:

    Some very interesting replies, and I have already learnt a lot, but none of them seem satisfactory to me, and many simply fail because of my locale — I am in the UK, so any solution using DATEVALUE will assume that the input format is DD/MM/YYYY and hence fail in locales using that structure as their standard, as will any that coerce the result into a date (eg using “0+”).

    I also assumed that the homework required a date (ie a serial date) as opposed to simple text, but I could be wrong. (My assumption here is reinforced by the cells being already formatted by Chandoo as Date).

    Finally, I assumed that the formula would have to cope with other strings containing “/”, eg “I said /what/?”, so I was looking for a formula containing “??/??/????” (I would have preferred a tighter constraint but as noted elsewhere such pattern matching is not available in Excel formulae).

    Anyway, my offering is
    =IFERROR(DATE(MID(B5,6+SEARCH(“??/??/????”,$B5),4),MID(B5,SEARCH(“??/??/????”,$B5),2),MID(B5,3+SEARCH(“??/??/????”,$B5),2)),”no date”)

    It only allows dates in the MM/DD/YYYY format, it returns a date (as opposed to a string that looks like a date), and it works in any locale (I think — I can’t check!).

    I am hoping there is a shorter formula that meets my assumptions.

  42. Lewis Kirby says:

    Sorry, formula chopped up a bit. It is, on two lines, as follows:

    =IFERROR(DATE(MID(B5,6+SEARCH(“??/??/????”,$B5),4),MID(B5,SEARCH(“??/??/????”,$B5),2),
    MID(B5,3+SEARCH(“??/??/????”,$B5),2)),”no date”)  

  43. Rob Easton says:

    {=IFERROR(DATEVALUE(MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),99)),10)),”n/a”)} works

  44. Vaf? says:

    {=IFERROR(MID(B4;MATCH(“B*”;IFERROR(“B”&DATEVALUE(MID(B4;ROW($A$1:$A$500);10));””);0);10);”N.A.”)}

    Supposed text has not more than 500 characters.

  45. Doosha says:

    A more challenging question is to select all dates within a cell given a specific date format dd/mm/yyyy for example. I can do it with a UDF, but that’s cheating.
    E.g.
    “Let me ask my wife, No wait, I think it is 07/01/1981 or 09/01/198″
    Returns
    “07/01/1981, 09/01/1981″

  46. Bermir says:

    =IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10),””)

    This is the same as Rogers’ elegant solution but slightly shorter, omitting the DATEVALUE, which does not seem necessary…  
     

    • Bermir says:

      =IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10),””)*1

      Same as before but the added “*1″ turns it into a number data type…

      • Lewis Kirby says:

        Sorry, but again this does not work in my locale — it says the answer is 7 January 1981.

        • Bermir says:

          Yes, I tried but in the end came up with the same formula as the one you originally posted using DATE. If there is a shorter one, I’d like to see it too…

  47. Bermir says:

    Sorry, this last one should have been:

    =IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10)*1,””)

  48. nizar ahamed khan says:

    sir,
    simply i used right, mid, mid, left, right(i used first row ),right, formulas in each row in colume C,
     thank you sir,
    nizar ahamed khan.k 

  49. Mohammed Mustafa says:

    Hi,

    Here is my solution….
    IFERROR(MID(B4,FIND(“/”,B4)-2,10),”No Dates here”)

      Mohammed Mustafa

  50. Rosco says:

    Thanks Chandoo,

    Mine would be:

    =IFERROR(- -(MID(B4,FIND(“/”,B4)-2,10)),”Not Available”)
    :)

     

  51. SUNIL says:

    When is your date of birth?
    DOB

    My date of birth is 07/01/1981
    07/01/1981

    Hmm, 07/01/1981, I think that is when I was born
    Hmm, 07/01/1981

    Oh wait, was it 07/01/1981? I think so
    ait, was it 07/01/19

    07/01/1981 that is when I landed here.
    07/01/1981

    Date of birth?!? What is that now?
     

    Let me ask my wife, No wait, I think it is 07/01/1981
    07/01/1981

  52. Kuldeep says:

    =IFERROR(MID(B9,(SEARCH(“/”,B9)-2),10),”-”)

  53. Manick says:

    Assuming that the date format is always dd/mm/yyyy, I used this formula:

    =IFERROR(MID(B4,FIND(“/”,B4)-2,10),””) 

  54. RAHUL AGGARWAL says:

    =IFERROR(MID(B4,FIND(0,B4,1),10),””)

  55. Shweta Jain says:

    =IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10),””)

  56. Senthilkumar RM says:

    =MID(B9,(FIND(“/”,B9)-2),10)

  57. Rajinikanth says:

    {=MID(B4,MATCH(TRUE,ISNUMBER(1*MID(B4,ROW($1:$255),1)),0),10)}

  58. Rajinikanth says:

    =MID(B7,SEARCH(“??/??/????”,B7),10)

  59. K Mohan says:

    =IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),”No date in the cell”)

  60. Yogesh says:

    =IFERROR(RIGHT(LEFT(B4,SEARCH(“/”,B4)+7),10),”Date Not found”)

  61. ran says:

    Assuming for any type of number :  

    Start position of a numeric  from left

    {=MATCH(1,–ISNUMBER(MID(B4,ROW($A$1:$A$2000),1)+0),0)}

    End Position of  numeric from Right :

    {=MATCH(1,–ISNUMBER(LEFT(RIGHT(B4,ROW($A$1:$A$2000)),1)+0),0)}

    above two coupled with mid and len command should give the desired result 

  62. vishwa says:

    Hi
    =IFERROR(MID(B4,FIND(“/”,B4)-2,10),”There is no date in this Text”)

  63. Imran says:

    =MID(B1, SEARCH(“??/??/????”, B1, 1), 10)
    or
    =DATEVALUE(MID(B1, SEARCH(“??/??/????”, B1, 1), 10))

    • Lewis Kirby says:

      Okay, I’ll stop replying after this one but: sorry, but again the second solution does not work in my locale — it says the answer is 7 January 1981. This will happen for any formula that uses DATEVALUE or coerces a numeric into a date. To make it work internationally the solution has to explicitly mark the three parts of the string as Month, Day, Year.

      Your first solution works well if the answer can be text that looks like a date, but you cannot do any arithmetic on it (well, no arithmetic that works in every locale).

      Signing off now ;-) 

  64. ZORRO2005 says:

    Hello from Russia:
    =LOOKUP(9^9,MID(B4,ROW(INDIRECT(“1:”&LEN(B4)-9)),10)*1)

  65. Deepanshu says:

    =IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),”No DOB present”)

  66. Fred L. says:

    Not sure this exact formula has been found but if not here it is:

    =MID(B3,FIND(CHAR(47),B3)-2,10)

  67. Kiev says:

    I would like to use this one, in case there is any empty cell in the text ring,

    MID(TRIM(CLEAN(B4)),FIND(“/”,TRIM(CLEAN(B4)))-2,10)

  68. Manisha Nanda says:

    1. convert to lowercase
        lower(A2)
    2. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(B2,””,””),”a”,””),”b”,””),”c”,””),”d”,””),”e”,””),”f”, “”),”g”,””),”h”,””),”i”,””),”j”,””),”k”,””),”l”,””),”m”,””),”n”,””),”o”,””), “p”,””),”q”,””),”r”,””),”s”,””),”t”,””),”u”,””),”v”,””),”w”,””),”x”,””),”y”, “”),”z”,””),”?”,””),”!”,””),”,”,””)

    A lengthy substitution though…!
     
    But it works ..:)
    Thanks Chandoo!!

  69. Jitendra says:

    It should be like this :
     
    =IFERROR(MID(B4,SEARCH(“/”,$B4)-2,2) & “/” & MID($B4,SEARCH(“/”,$B4)+1,2) & “/” &MID($B4,SEARCH(“/”,$B4)+4,4),”Date Not Found.”)
     
     
    *$B4 – the cell containing text & date mixture.

    • Jitendra says:

      =IFERROR(MID($B4,SEARCH(“/”,$B4)-2,2)&”/”&MID($B4,SEARCH(“/”,$B4,SEARCH(“/”,$B4)+1)+1,2)&”/”&MID($B4,SEARCH(“/”,$B4,SEARCH(“/”,$B4)+1)+1,4),”Date not found.”)

  70. Robert Clark says:

    =IF(ISERROR(FIND(“/”,B4)),”No date found”,DATE(MID(B4,SEARCH(“??/??/????”,B4)+6,4),MID(B4,SEARCH(“??/??/????”,B4),2),MID(B4,SEARCH(“??/??/????”,B4)+3,2)))

    I think that this may overcome Lewis’s comments about date format/regions (seems to work that if teh datestring is 06/31/1981, it converts is to 1st July.

    • Lewis Kirby says:

      Robert, Thanks, yes this gets around the regions/locale problems I have been highlighting, and is similar to the one I posted a few days ago, plus it actually delivers a date as opposed to a string that looks like a date.

      • Kyle McGhee says:

        Lewis
        =VALUE(TEXT(VALUE(MID(B4, SEARCH(“??/??/????”, B4, 1), 10)),”mm/dd/yyyy”))
         
        fyi, to make any string date into a value for arithmetic, you can wrap the text with the Value() function.
        Use the Text function to shift the MM & DD around.

        • Lewis Kirby says:

          That’s clever — it only swaps the MM and DD around if they need to be swapped! I knew about using VALUE(), but using TEXT() like that leaves the MM and DD as they are if you are in the USA (etc) but swaps them if you are in the UK.

          You just need to wrap it in IFERROR to cater for missing or invalid dates and you’re done.

          My only nit-pick is that it relies on the Short Date format being DD/MM/YYYY or MM/DD/YYYY. I once worked on a client’s machine where their short format was YYYY-MM-DD and this solution would not work for them (whereas solutions mentioned above using the DATE function to pick out the three components of date would — as opposed to relying on default date formatting). But your solution will work for the vast majority of cases — thank you!

  71. Philip says:

    =IFERROR((LEFT((RIGHT(B4,(LEN(B4)-(FIND(“/”,B4)-3)))),10)),””)

  72. Aaron says:

    Following the advice of extracting numbers from text:

    =IFERROR(MID(B4;MIN(IFERROR(FIND(valD;B4);””));SUMPRODUCT(COUNTIF(valD;MID(B4;ROW($A$1:$A$200);1))));””)

    where valD = {1,2,3,4,5,6,7,8,9,0,”/”}

  73. Prasanna venkatesh says:

    =IFERROR(MID(B4,FIND(“/”,B4)-2,10),””)

  74. Pablo says:

    Hi Chandoo,
    While these formulas work great extracting dates formatted, I have a bigger date challenge where there is no format. The file contains over 10000 lines and the date formats are as many as 1000. For example your 07/01/1981 can be typed like this:
    1981 July first, 1 Jul 81, July 1 81, 81 7 1, 81 1 7, 81-1-7, 1981-Jul-01, 1981/07/01, 1981, Jul, July, etc. You got the idea. By the way the dates are between 2010 and Aug 2012.
    My approach was to create a table with as many combinations I found. Then I wrote a VBA application that populates an emptly column with the 1st match of the table date. Is not 100% accurate but works most of the time.
    Is there a better solution?
    Thanks,
    Pablo

  75. Oli says:

    =DATEVALUE(MID($B4,MIN(IFERROR(FIND(CHAR(ROW($48:$57)),$B4),””)),10))

  76. Uriel says:

    Hello from Mexico!!

    =DATEVALUE(IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),”..”))

  77. Michael Pennington says:

    I had the same thought as Shadow Jam and after a bit of trial and error, I found a way to do it with the Code function.  It isn’t pretty, but it is working for me.  Of course, it is coming back with text, so it would require a bit more work if you wanted to use the dates and I didn’t put the if error around it, but that would get rid of the NAs.

    =MID(B4,MATCH(1,(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))<58)*(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))>47),0),10)

    Basically, just looking for a code greater than 47 and less than 58 (ANSI between 0 and 9), finding the first occurrence and returning 10 characters from the beginning of that instance.  Array formula and it isn’t very elegant, but it gets the job done.

    • Michael Pennington says:

      Just couldn’t leave it alone.  This gets rid of the NAs and converts it to a usable date:

       =IFERROR(DATEVALUE(MID(B4,MATCH(1,(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))<58)*(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))>47),0),10)),””)

      Although, this means the date would have to be in the format as shown–i.e. MM/DD/YYYY.
       

      • Lewis Kirby says:

        Sorry, I cannot resist — your use of DATEVALUE() also assumes that the default system date format is MM/DD/YYYY, so on my PC in the UK, the /result/ of your formula is 7th January 1981.

        • Michael Pennington says:

          Good catch, Lewis.  I guess I should have stuck with my first instinct to use the Date function:

           =IFERROR(DATE(RIGHT(MID(B4,MATCH(1,(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))<58)*(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))>47),0),10),4),LEFT(MID(B4,MATCH(1,(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))<58)*(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))>47),0),10),2),MID(MID(B4,MATCH(1,(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))<58)*(CODE(MID(B4,ROW($A$1:$A$100),LEN(B4)))>47),0),10),4,2)),””)

          Kind of turns it into a monster formula, though! 

  78. dwi budi says:

    my solution is:
    =IF(ISERR(MID(B4;FIND(“/”;B4;1)-2;10)*1);””;MID(B4;FIND(“/”;B4;1)-2;10)*1)

  79. Chris says:

    Uh…
    I have a solution and it is likely quite similar to some posted above.
    However, I use Excel on a German PC, so I only have the German formula names.
    is there an easy way to find out the English equivalents?
    Cheers,
    Chris

    • Henk Blanksma says:

      I have a sheet with all(?) commands translated in 7 languages, even Russian.
      If you you provide me with you  emailadres, I’ll send it to you.
      Perhaps Chandoo can publish it on his site. 

  80. Matt Knight says:

    Simply:
    =+IFERROR(VALUE(MID(B4,FIND(“/”,B4)-2,10)),”No date entered”), on the assumption that the input is always mm/dd/yyyy rater than /yy

  81. =IFERROR(MID(B4,IFERROR(SEARCH(“0″,B4),SEARCH(“1″,B4)),10),””)

  82. Matt says:

    =iferror(LEFT(RIGHT(B4,LEN(B4)-FIND(“/”,B4)+3),10),”No DOB found”)

  83. ET says:

    =IFERROR(VALUE(MID(B8,SEARCH(“??/??/????”,B8),10)),””)

  84. PB says:

    =IF(LEN(B4)-LEN(SUBSTITUTE(B4,”/”,””))=2,MID(B4,FIND(“/”,B4)-2,10),”No Date Found”)

  85. Roseus says:

    =IFERROR(EDATE(LEFT(RIGHT(B4,LEN(B4)-(SEARCH(“??/??/????”,B4)-1)),10),0),”No Date Present”)

    Edate validates it as a seemingly possible date, and catches entries like:

    - Hope it doesn’t re/tu/rn this entry.
    - What is 20/50?

  86. Kaushik Ranganathan says:

    =IF(ISERROR(MID(B4,SEARCH(“??/??/????”,B4,1),10)),””,TEXT(MID(B4, SEARCH(“??/??/????”,B4,1), 10),”MM/DD/YYYY”))

    Using the TEXT function to convert the format so to “MM/DD/YYYY” and I guess this ensures that the date is displayed in the cell based on the date format on the system. :) 

  87. Brice says:

    Can you use these type of string functions when evaluating a cell that has a formula?  For instance I have a cell that has a function in it that is linked to another workbook (=SUMIFS(‘K:\CF Marketing\Month_End\Brice\FI Interface\[FI_Interface_20 Aug12.xlsx]L043 Backup’!$N:$N) and I would like to extract the file name out of the formula.  Would that be possible using functions or would I need to use VB?

    • Michael Pennington says:

      I have tried to extract a string from a formula without VBA and have yet to find a way to do it.  Over in the MrExcel forums, I found a good bit of code that is pretty clean and easy to use, though: http://bit.ly/O8GtFi  

      Function CellFormula(Rng As Range) As String
               CellFormula = Rng.Formula
      End Function
        
      I find it useful whenever I am dealing with poorly designed spreadsheets with useful numbers or text manually keyed into formulas.   

  88. Weow says:

    =MID(B4,FIND(“/”,B4)-2,10)

  89. Ekaterina says:

    =IF(IFERROR(FIND(“/”;B4;1);0)=0;”no date”;LEFT(RIGHT(B4;LEN(B4)-IFERROR(FIND(“/”;B4;1);0)+3);10)) – just another way to extract date :)

  90. Henk Blanksma says:

    I found it more by accident.
    So I can’t say it my merit.
    It is sooo shamefull simple:

    =SEARCH(0,B4) 
    and it came up with the dates!!! 

  91. Andreas says:

    =MID(A2;SEARCH(“/”;A2)-2;10)

  92. Pete says:

    =IF(ISERROR(MID(B9,FIND(“/”,B9)-2,10)),”07/01/1981″,MID(B9,FIND(“/”,B9)-2,10))

  93. Nandan Menon says:

    I find this as the simplest formula for this purpose:
    IFERROR(DATEVALUE(MID(A2,FIND(“/”,A2)-2,10)),”No Date here!!!”)

    Thank you 

  94. Edwin says:

    LOL,

    long… but it gets the job done!

    AS TEXT
    =IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7)), “No DOB”, LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7)) 

    AS VALUE
    =IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7)), “No DOB”, VALUE(LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7))) 

  95. Edwin says:

    =IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7)), “No DOB”, LEFT(RIGHT(LEFT(B4,FIND(“/”,B4,1)),3),2)&”/”&LEFT(RIGHT(B4,(LEN(B4)-FIND(“/”,B4,1))),7)) 

  96. edwin says:

    All,
     
    Please note that this website will convert your quotes… meaning when you type in a formula, you’re using Char(34). This website will convert your comment into char(147) and Char(148).
     
    So if you copy and paste any formula from this board, make sure you convert the quotes back to the excel standard.

  97. Assume that you have the data in in column A. insert the forumula in column B. but i have not used array formula.

    =IFERROR(LEFT(RIGHT(A4,LEN(A4)-(FIND(“/”,A4)-3)),10),”-”)

  98. samtheman says:

    =IFERROR(MID(B4,FIND(0,B4),10),”N/A”)

  99. aaa says:

    =IFERROR(TEXT(MID(B4,MATCH(1,ISNUMBER(MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1)+0)*1,0),10),”dddd,mmmm dd,yyyy”),”No date”)

  100. Rajesh says:

    =MID(B4,FIND(“/”,B4,1)-2,10)

  101. Ishu says:

    =iferror(VALUE(MID(B4,MATCH(TRUE,ISNUMBER(MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1)*1),0),SUM(–(ISNUMBER(MID(B4,ROW(INDIRECT(“1:”&LEN(B4))),1)*1))))),””)
     
    CTRL+SHIFT+ENTER

  102. Bharath.ch says:

    As simple as
     
    =IFERROR(LEFT(RIGHT(B6,((LEN(B6)-FIND(“/”,B6,1))+3)),10),” Date Not Available”)

  103. Arnab Sen says:

    =IFERROR(MID(B4,FIND(“/”,B4)-2,10),”No Date Found”)

  104. Rahim Zulfiqar Ali says:

    how to rewrite a word in excel, for example in Cell A1: RAHIM i have to change it like MIHAR
    is there any technique in excel?

  105. Chirayu says:

    There is one big issue Chandoo

    Search function cannot use an Array as far as I know.

    I have made both find & search function for the criteria below:
    ———————————————-
    my date of birth is 05/03/1989
    my date of birth is 05/03/89
    my date of birth is 05-03-1989
    my date of birth is 05-03-89
    my date of birth is 05/03/1989 & I like it
    my date of birth is 05/03/89 & I like it
    my date of birth is 05-03-1989 & I like it
    my date of birth is 05-03-89 & I like it
    ———————————————-

    Find:
    =IFERROR(IFERROR(IFERROR(MID(A2,FIND(“/”,A2,1)-2,10)*1,MID(A2,FIND(“/”,A2,1)-2,8)*1),MID(A2,FIND(“-”,A2,1)-2,10)*1),MID(A2,FIND(“-”,A2,1)-2,8)*1)

    Search:
    =IFERROR(IFERROR(IFERROR(MID(A2,SEARCH(“??/??/??”,A2,1),10)*1,MID(A2,SEARCH(“??/??/????”,A2,1),8)*1),MID(A2,SEARCH(“??-??-??”,A2,1),10)*1),MID(A2,SEARCH(“??-??-????”,A2,1),8)*1)

    However how would we tackle these variations:

    05-Mar-1989
    05-Mar-89

  106. Korpo says:

    Hi all,
    I know it is old one but have someone of you tried all those functions against a cell with just a date? like “07/01/1981″? or “07-01-1981″ my excel shows all the time error. Just do not see the ??/??/???? pattern.
    My spaghetti solution:
    =IF(ISNUMBER(IFERROR(DATEVALUE(MID($A2,IFERROR(SEARCH(“??/??/????”,$A2,1),0)+IFERROR(SEARCH(“??-??-????”,$A2,1),0),10)),A2))=FALSE,”text”,IFERROR(DATEVALUE(MID($A2,IFERROR(SEARCH(“??/??/????”,$A2,1),0)+IFERROR(SEARCH(“??-??-????”,$A2,1),0),10)),A2))

  107. Syedali says:

    =IF(COUNT(SEARCH(“07/01/1981″,B8)),”07/01/1981″,””)

  108. Vlad says:

    Any answer to this from Chandoo? :D

    • Hui... says:

      @Vlad
      Work through the comments above
      You will see simple formulas which work in some but not all cases
      You will also see more complex formulas that work in all cases

  109. SUMIT CHANDRA says:

    Try it…working properly….
    =IFERROR(MID(B2,FIND(“/”,B2,1)-2,10),””)

  110. SUMIT CHANDRA says:

    Try it…working properly….
    =IFERROR(MID(B4,FIND(“/”,B4,1)-2,10),””)

Leave a Reply