Finding date in a string

[bpotter]

I have an auto generated workbook that I am pulling reports from. In
the very first row I have the following string:
///CST Period Variance Report - Cement (Initial Date: 1/14/2009
Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated
Wells)\\\

What I would like to do is pull the first date after initial date and
find that date on another workbook.
I can't even think of how to start. Please help.

Bryan

 

[Joel]

Try this code. I could of looked for the colon instead of Initial date but
incase there was more than one colon I did a little extra checking.

MyStr = "///CST Period Variance Report - Cement (Initial Date: 1/14/2009 " & _
"Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated" & _
"Wells)\\\"

StartPos = InStr(MyStr, "Initial Date:")
MyStr = Mid(MyStr, StartPos)
StartPos = InStr(MyStr, ":")
MyStr = Trim(Mid(MyStr, StartPos + 1))
EndPos = InStr(MyStr, " ")
MyDate = DateValue(Trim(Left(MyStr, EndPos - 1)))

 

[bpotter]

Can I change the Mystr equal to cell "a1"?

 

[bpotter]

It is giving me a compile error. Array expected.

 

[Joel]

I changed only My
Str to equal Range("A1")

'These lines are commented
'MyStr = "///CST Period Variance Report - Cement (Initial Date: 1/14/2009 "
& _
' "Compare Date: 1/13/2009 (Yellow/Change) | Gross Volumes | Operated" & _
' "Wells)\\\"

MyStr = Range("A1")
StartPos = InStr(MyStr, "Initial Date:")
MyStr = Mid(MyStr, StartPos)
StartPos = InStr(MyStr, ":")
MyStr = Trim(Mid(MyStr, StartPos + 1))
EndPos = InStr(MyStr, " ")
MyDate = DateValue(Trim(Left(MyStr, EndPos - 1)))

 

[Rick Rothstein]

What is giving you a compile error? It is always a good idea to show the
formula or code that is giving you an error so we can see exactly what you
did.

As for you first question (in your other follow up post), you would do this
to get Mystr equal to what is in A1...

Mystr = Range("A1").Value

and use this line of code in place of where Joel first assigns the text
string to the variable.

Oh, and I had a follow up question for you... the text you showed in the
cell... is there any manual linefeeds in there (as my news reader shows) or
is that a single line of text?

 

[Gary''s Student]

Consider:

Function getdate(r As Range, which As Integer) As Date
s = Split(r.Value, " ")
i = 1
For j = 0 To UBound(s)
If IsDate(s(j)) Then
If i = which Then
getdate = s(j)
Exit Function
Else
i = i + 1
End If
End If
Next
End Function

So that if A1 contains:

Now is the time 1/24/2009 for all 12/25/1945 men to

then
=getdate(A1,1) will return 1/24/2009
and
=getdate(A1,2) will return 12/25/1945

 

[bpotter]

No manual linefeeds.
Gary's solution worked great for me.

Thank yuo so much for all the help guys.

 

[bpotter]

With the getdate function where does it store the date so I can find
it in another workbook?

I generate a report every morning with today and yesterday's date. I
then sort the main sheet to several routes and do calculations for
downtime. I then copy all downtime to a corresponding workbook the has
the months downtime in it.

What I would like to happen though is to be able to make it backwords
compatible in case I miss a day and generate a report for last week it
will find the right date in the other workbook and copy my downtime on
the correct sheet.



Bryan

 

[Rick Rothstein]

With the getdate function where does it store the date so I can

find it in another workbook?

It is a function... you can assign its output to your variable...

Mystr = getdate(Range("A1"), 1)

 

[Rick Rothstein]

No manual linefeeds.

The perhaps you (and Joel or Gary''s Student as well) might find this
one-line solution of interest...

MyDate = Split(Trim(Split(Range("A1").Value, "Initial Date:")(1)))(0)

If there is **always** a space following the "Initial Date:" piece of text,
then the above can be shortened a little bit...

MyDate = Split(Split(Range("A1").Value, "Initial Date: ")(1))(0)

 

[bpotter]

These were all great solutions guys thank you soo much for all the
help.

 

[Rick Rothstein]

Here is a completely different approach (although if I had to guess, I'd say
your approach may be slightly efficient)...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For X = 0 To UBound(Words)
If Not IsDate(Words(X)) Then Words(X) = ""
Next
GetDate = Split(WorksheetFunction.Trim(Join(Words)))(Which - 1)
End Function

 

[Rick Rothstein]

This is basically your routine, but structured around a For Each loop...

Function GetDate(ByVal S As String, Which As Long) As Date
Words = Split(S)
For Each W In Words
If IsDate(W) Then
X = X + 1
If X = Which Then
GetDate = CDate(W)
Exit For
End If
End If
Next
End Function

 

[Gary''s Student]

Thanks Rick

Both suggestions are interesting alternatives.