CODEWITHSUNDEEP
vbaexcel
danjah
danjah

Reputation: 1246

Recognize dates placed randomly within string

I'm looking for a way to recognize date stamps within a string in an Excel-cell, using VBA, and then determine which is closest to todays date. The cell is used for all sorts of comments.

The date stamps will always be looking like this:

dd.MM.YYYY or d.M.YYYY

There could be any number of stamps like this within the same cell.

Example

Lets say that cell B5 has the following contents:

12.3.2015 I have done this and this. 20.11.2015 Done something more.

I would now like to recognize the two date stamps, and find out how many days separates today from the freshest one.

That last part i know can be sorted out with something like DateDiff("d", y.Sheets("Sheet1").Range("B5").Value, Date), although that wouldn't work in this example, being that the cell contains a string.

Any ideas?

Upvotes: 0

Views: 94

Answers (2)

jleach
jleach

Reputation: 7800

Use Regular Expressions, they're made for this sort of thing. I won't get into extreme details on how all the RegEx stuff works, the basic gist of RegEx is that there's a string of characters, where each character means something specific about the search you want it to perform. For example, say Find all strings that consists of a group of numbers between 1-12 followed by a (pick your date delimiters), followed by one or more numbers that is within 1 and 31... etc.)

RegExLib.com usually has some decent solutions for common problems such as this. Here's one we can try: http://regexlib.com/REDetails.aspx?regexp_id=932 (but we'll remove the leading ^ and trailing #):

([0]?[1-9]|[1][0-2])[./-]([0]?[1-9]|[1|2][0-9]|[3][0|1])[./-]([0-9]{4}|[0-9]{2})

Validate american date formats: mm/dd/yyyy or m/d/yy or m.d.yyyy with separators: . - / Valid dates only! m (1-12)/ d (1-31)/ y (0..)

Then we whip up some VBA for it. I prefer late binding this so I don't need an added reference just for one regex check:

Public Function GetDelimitedDatesInString(StringIn As String) As String
  Dim ret As String
  Dim matches As Object
  Dim regex As Object
  Dim pattern As String
  Dim i As Integer, j As Integer

  pattern = "([0]?[1-9]|[1][0-2])[./-]([0]?[1-9]|[1|2][0-9]|[3][0|1])[./-]([0-9]{4}|[0-9]{2})"

  Set regex = CreateObject("VBScript.RegExp")

  regex.pattern = pattern
  regex.Global = True


  Set matches = regex.Execute(StringIn)

  For i = 0 To matches.Count - 1
    ret = ret & ";" & matches(i).Value
  Next i

  If Len(ret) Then ret = Mid(ret, 2) 'trim leading delim

  GetDelimitedDatesInString = ret

End Function

Upvotes: 1

user3819867
user3819867

Reputation: 1118

Sub e(s As String)
Dim myarr() As String, mydate(50)
j = 0
datestr1 = "##.#.####*"
datestr2 = "##.##.####*"
For i = 1 To Len(s)
    If Mid(s, i) Like datestr1 Or Mid(s, i) Like datestr2 Then
        myarr = Split(Mid(s, i), ".")
        dateday = myarr(0)
        datemonth = myarr(1)
        dateyear = Left(myarr(2), 4)
        mydate(j) = DateSerial(dateyear, datemonth, dateday)
        MsgBox mydate(j)
        j = j + 1
    End If
Next i
End Sub

This is not the expected output but could get you going, the point is if the string matches what you're looking for you create an array with the date being in the first three array items.

Upvotes: 0

Related Questions