CODEWITHSUNDEEP
vbaexcel
Gregory
Gregory

Reputation: 315

Find and extract date from string not working?

I am attempting to determine if the date that is inside of a string containing other text is less than the date in another cell, but for some reason, the function never seems to hit on the date formula, even when the date format matches my Like statement conditions.

Here is an example of what one of the cells looks like that I am attempting to extract from 1/9/2018 5:02AM Benjamin Button

Please see the code below

Sub findAndCheck
 for i = 2 to lastrow ''Lastrow declared and set, just not shown here [i is global]
  if Getdate(cells(i,68).value2) then
    comments = "True"
  end if
 next i
End Sub


Public Function Getdate(expression As Variant) As Boolean
Dim dateconvert As Date  
  If (expression Like "*#/#/####*") Then
    dateconvert = CDate(Left(expression, 8))
      Getdate = CDate(expression) < CDate(Cells(i, 66))
  ElseIf (expression Like "*##/#/####*") Or (expression Like "*#/##/####*") Then
        dateconvert = CDate(Left(expression, 9))
        Getdate = CDate(expression) < CDate(Cells(i, 66))
  ElseIf (expression Like "*##/##/####*") Then
      dateconvert = CDate(Left(expression, 10))
      Getdate = CDate(expression) < CDate(Cells(i, 66))
  End If

End Function

Upvotes: 2

Views: 200

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

To extract a date from an expression similar to what you show (where the date is either at the beginning, end, or separated by a space from the rest of the string, you can use:

Option Explicit
Function GetDate(sExpression As String) As Date
    Dim v, w
v = Split(sExpression, " ")
For Each w In v
    If IsDate(w) Then
        GetDate = CDate(w)
        Exit Function
    End If
Next w

End Function

The function will return a zero if there is no date

And, to compare two cells, something like:

Sub findAndCheck
 for i = 2 to lastrow ''Lastrow declared and set, just not shown here [i is global]
  if Getdate(cells(i,68) > getdate(cells(i,66) and _
     getdate(cells(i,68) <> 0 and getdate(cells(i,66) <> 0 _
 then
    comments = "True"
  end if
 next i
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

isdate(split("1/9/2018 5:02AM Benjamin Button", chr(32))(0)) should determine whether you have a leading date or not.

Public Function Getdate(expression As Variant, _
                        optional i as long = 2) As Boolean
    If isdate(split(expression, chr(32))(0)) Then
        Getdate = CDate(split(expression, chr(32))(0)) < CDate(Cells(i, 66))
    End If  
End Function

Upvotes: 2

neophlegm
neophlegm

Reputation: 373

Your problem lies here:

Getdate = CDate(expression) < CDate(Cells(i, 66))

You won't be able to evaluate CDate(expression) as the full text of expression still contains something other than the date stamp. I imagine you want instead to check if your date conversion is less than the value in that cell:

Getdate = dateconvert < CDate(Cells(i, 66))

You also need to declare i somewhere. Presumably you want it as another argument in your function (for eg):

Public Function Getdate(expression As Variant, i as integer) As Boolean

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You didn't include i as an argument for your function, so I did.

Also, just check if your expression contains a space, then perform a split on that.

Public Function Getdate(expression As Variant, i As Long) As Boolean

    Dim rawDate As Date
    If InStr(1, expression, " ") > 0 Then
        rawDate = CDate(Split(expression, " ")(0))
    End If

    Getdate = rawDate < CDate(Cells(i, 66))

End Function

You should also perform a check of rawDate = 0, which would mean that your string didn't contain a date at all.

Upvotes: 2

Related Questions