Solved: Extract Date

[James Niven]

Hi All,

I wish the extract the date only from text in column E. The date is like 8/31. I wish to place the date in column G concatenating the column year, so for this example I wish to return 08/31/2009.

Please see the attached sample. I have got so far, but still not hitting what I want.

James Niven
Cedar Creek, TX

[lucas]

Hi James, there is no 2009 in the example to draw from....

mid might be a better choice but I'm no formula expert:

=MID(E2,12,11)

[James Niven]

Lucas,

Look at column F, this is the year to draw from!

[James Niven]

Lucas,

Thanks for your input.

Your example will work if every cell has 12 characters from the left every time before the date, but this is not always the case.

Thanks

[GTO]

Greetings James,

With only one example of the string that we're trying to find something we can coerce to a date in, not sure how this will fair...

Possibly as a UDF:

Function DateCoerce(CellAddress_String As String, CellAddress_Year As String) As Date
Dim REX As Object ' RegExp
Dim rexMatchCollection As Object ' MatchCollection
Dim ary As Variant
Dim lMonth As Long
Dim lDay As Long

Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = False
'// IF I have this correct... //
'// word boundary, 1-2 digits, solidus, 1-2 digits, word boundary. //
.Pattern = "\b\d{1,2}/\d{1,2}\b"
If .Test(CellAddress_String) Then
Set rexMatchCollection = .Execute(CellAddress_String)
ary = Split(rexMatchCollection(0), "/")
lMonth = CLng(ary(0))
lDay = CLng(ary(1))
DateCoerce = DateSerial(Trim(CellAddress_Year), lMonth, lDay)
Else
DateCoerce = DateSerial(1900, 1, 1)
End If
End With
End Function


Many still cannot read 2007 format, attached is same as at #1 (with code):

Does that work?

Mark

[James Niven]

GTO,

You have come through for me again, I appreciate it!!!

So, if I understand =Datecoerce(E2,F2) is a short cut to the code you supplied? I guess I dont quite understand how it relates to the code.

But, it works like a charm, just what I wanted.

Thanks

[GTO]

Hi James,


...So, if I understand =Datecoerce(E2,F2) is a short cut to the code you supplied? I guess I dont quite understand how it relates to the code....

Well, hopefully someone will give a more articulate/educated response, but here's what I can offer:

I don't know if I'd refer to it as a 'shortcut' per se, but it does of course provide a way of calling the function from the sheet and returning the value to the calling cell. Think of it similar to a worksheet function like:


=IF(A1>B1,"Yes","No")

...except of course our function is User Defined.

In our case, we are passing the text/value from the two cells to the function, where we use Regular Expressions to find the first match to the pattern. This is then split and used for the month and day.

Reference UDFs, from my very limited understanding, they can get expensive if always recalculating (volatile), but in my wee bit o' testing, I believe this one only recalcs (runs) once per arg changed.

Not sure if that is of much help, but hope so,

Mark

[James Niven]

GTO,

I guess I understand some of your detailed description, hey but I am grateful it works for my purpose.

Thanks again GTO!!

James