Extract dates from text string

[Tvynn]

Hi, I'd like to extract out the dates from text string by using formula instead of text to column, is it possible? After that, i'd like to calculate the Number of Days Between Two Dates, and shows it blank if nothing to deduct instead of showing date. Appreciate your kind help, thanks!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 20px"><col width="628"><col width="58"><col width="58"><col width="58"><col width="58"><col width="63"><col width="63"><col width="63"><col width="74"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Original text[/TD]
[TD="bgcolor: #FFFF00"]date 1[/TD]
[TD="bgcolor: #FFFF00"]date 2[/TD]
[TD="bgcolor: #FFFF00"]date 3[/TD]
[TD="bgcolor: #FFFF00"]date 4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7E1CD]#B7E1CD[/URL] , colspan: 3"]Number of Days Between Two Dates[/TD]
[TD="bgcolor: #C9DAF8"]Number of Days Between First & Last Date

[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a@mail.com/hard(amy/2jan19);b@mail.com/hard(ken/20dec18);c@mail.com/hard(jane/01nov18);d@mail.com/hard(bill/15oct18);[/TD]
[TD="align: right"]2Jan19[/TD]
[TD="align: right"]20Dec18[/TD]
[TD="align: right"]01Nov18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]a@mail.com/hard(amy/3jan19);b@mail.com/hard(jane/5dec18);c@mail.com/hard(kate/15oct18);[/TD]
[TD="align: right"]3Jan19[/TD]
[TD="align: right"]05Dec18[/TD]
[TD="align: right"]15Oct18[/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]a@mail.com/hard(amy/9jan19);b@mail.com/hard(jane/8dec18);[/TD]
[TD="align: right"]9Jan19[/TD]
[TD="align: right"]8Dec18[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Rick Rothstein]

@Rick, this is very useful, im looking for this same solution but my source is different, how should be modified if i need to extract dates from a text cell like this:

[TABLE="width: 450"]
<tbody>[TR]
[TD="class: xl63"]Pardot - Database - Automation Rules (unlimited)
1 license x $0.00/mo x 8 months

Term: 5/31/2019 - 1/30/2020[/TD]
[/TR]
</tbody>[/TABLE]


The word "term", will not always be there, thats why Im looking for a way to recognize the date format and extract 1 by 1 into separate columns.

Is the date range always at the end of the text like your single example shows?

And what output do you want for those two dates... each shown date in its own cell, those dates and all the dates between them in their own cells, something else?

 

[davidmg1982]

Is the date range always at the end of the text like your single example shows?

And what output do you want for those two dates... each shown date in its own cell, those dates and all the dates between them in their own cells, something else?

Not necessarily, it can be any where in the text, each one of the dates on its own cell, like this:

[TABLE="width: 380"]
<tbody>[TR]
[TD]Oracle Hyperion Planning Plus -
Application User Perpetual

Support Service Number: 6558228

Term: 06/01/2018 - 05/31/2019

Refer attached document for detailed information[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]5/31/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Term: 06/01/2018 - 05/31/2019

Your first example showed the date formats without the leading zero whereas above you show it with them. Is there any general rule I can lock onto regarding the formats for the dates?

 

[davidmg1982]

Your first example showed the date formats without the leading zero whereas above you show it with them. Is there any general rule I can lock onto regarding the formats for the dates?

You are right, well my ultimate goal is to have the outcome formated as MM/DD/YYYY

Dates on the source may have or not the starting "0"

Thanks Rick.

 

[Rick Rothstein]

You are right, well my ultimate goal is to have the outcome formated as MM/DD/YYYY

Dates on the source may have or not the starting "0"

I am not sure how to do what you want using just Excel functions, so I designed a UDF (user defined function) which should do what you want. The function takes two arguments... the first is the text to process (just specify the cell address for this) and the second is the date from the span that you want (use 1 for the date on the left and 2 for the date on the right). Here is the code (note I have assumed you will always have 4-digit years in your dates)...

Function DateSpan(ByVal S As String, WhichDate As Long) As Variant
  Dim X As Long, V As Variant
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9/]" Then Mid(S, X) = " "
  Next
  For Each V In Split(Application.Trim(S))
    If V Like "*#/#*/####" And IsDate(V) Then
      DateSpan = V
      If WhichDate = 1 Then Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DateSpan just like it was a built-in Excel function. For example, if you have one of your text values in cell A1, then put this formula in cell B1, copy it across to cell C1, then copy B1:C1 down to the bottom of your data...

=DateSpan($A1,COLUMN(B1)-1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[davidmg1982]

I am not sure how to do what you want using just Excel functions, so I designed a UDF (user defined function) which should do what you want. The function takes two arguments... the first is the text to process (just specify the cell address for this) and the second is the date from the span that you want (use 1 for the date on the left and 2 for the date on the right). Here is the code (note I have assumed you will always have 4-digit years in your dates)...

Function DateSpan(ByVal S As String, WhichDate As Long) As Variant
  Dim X As Long, V As Variant
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9/]" Then Mid(S, X) = " "
  Next
  For Each V In Split(Application.Trim(S))
    If V Like "*#/#*/####" And IsDate(V) Then
      DateSpan = V
      If WhichDate = 1 Then Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DateSpan just like it was a built-in Excel function. For example, if you have one of your text values in cell A1, then put this formula in cell B1, copy it across to cell C1, then copy B1:C1 down to the bottom of your data...

=DateSpan($A1,COLUMN(B1)-1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

You did it again, it works perfect. Thank you.