I have a Product Name like this. I run vba code for extracting date, am not getting the result, it shows error

Question

Code:

   Sub My_Date()

   Dim endRow As Long
     
     endRow = Cells(rows.Count, "B").End(xlUp).row

    ActiveCell.FormulaR1C1 = _
        "=DATEVALUE(IF(LEFT(RIGHT(LEFT(RC[1],FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5))-1),FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),4))-1),3)=TEXT(TODAY()-1,""Mmm""),RIGHT(LEFT(RC[1],FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5))-1),FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHA" & _
        "-1),IF(LEFT(RIGHT(LEFT(RC[1],FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),6))-1),FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5))-1),3)=TEXT(TODAY()-1,""Mmm""),RIGHT(LEFT(RC[1],FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),6))-1),FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),6))-FIND(CHAR(1),SUBSTITUTE(RC[1],""_"",CHAR(1),5)" & _
        """)))"
    
    range("B2").Autofill Destination:=range("B2:B" & endRow)
    
   End Sub

Replace `ActiveCell` with `Range("A2")` and all `B`s in the last line with `A`. — Vitalizzare, May 09 '22 at 10:32
p.s. Edit your question, please. Put the description in the body, not in the title. Add the description of en error. Structure your formula if it causes an error. — Vitalizzare, May 09 '22 at 10:41

score 1 · Answer 1 · answered May 09 '22 at 13:07

1

You could write your own function.

This will split your text by the _ delimiter and return the bit that can be turned into a date.

Sub Test()

    Dim endRow As Long
    With ThisWorkbook.Worksheets("Sheet1")
        endRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Range(.Cells(2, 1), .Cells(endRow, 1)).FormulaR1C1 = "=ExtractDate(RC2)"
    End With
    

End Sub

Public Function ExtractDate(Target As Range, Optional Delim As String = "_") As Variant

    Dim SplitText As Variant
    SplitText = Split(Target, Delim)
    
    Dim Itm As Variant
    For Each Itm In SplitText
        If IsDate(Itm) Then
            ExtractDate = CDate(Itm)
            Exit For
        End If
    Next Itm
    
    'If no date found return an #N/A error.
    If ExtractDate = 0 Then ExtractDate = CVErr(xlErrNA)

End Function

The cell reference in the Test procedure "=ExtractDate(RC2)" is in the R1C1 format - it means this row (where the formula appears), column 2.

answered May 09 '22 at 13:07

Darren Bartrup-Cook

18,362
1
23
45

There's a pitfall: `IsDate` won't recognize _May 03_ as a date in other then English locale. Is it possible to overcome this trap? I mean, how to make `IsDate` work with US locale? – Vitalizzare May 09 '22 at 14:15
@Vitalizzare I've no idea - I've only used UK Excel. Both `isdate("May 03")` and `isdate ("03 May")` worked in the immediate window, and both returned the same date when I used `CDate`. Does US return an error? – Darren Bartrup-Cook May 09 '22 at 14:26
1

Not US, and not an error but a False value. My locale is a mix of Ukraine and Russian. Sometimes I work with Polish. The best thing I could come up with is to use a `Range(...).NumberFormat = "[$-409]mmmm d"` kind of code with an appropriate number for locale as an intermediate aid. It's not efficient, so I'm looking for an alternatives. – Vitalizzare May 09 '22 at 15:10
p.s. by _other then English locale_ I meant non-English-language locales. Not US, not UK, not Canada etc. – Vitalizzare May 09 '22 at 15:15
It is appearing as #NAME? Date is in the text format – Karthik May 09 '22 at 15:46
Where have you added the code? It should be in a _normal_ module. Not in _ThisWorkbook_ or any specific sheet. – Darren Bartrup-Cook May 10 '22 at 13:37
@DarrenBartrup-Cook Whom do you ask? If you ask me, the answer is _module_ (which you call _normal_), not the code page of any Excel object like a sheet or a workbook. But it doesn't matter. `IsDate` and `CDate` rely on system settings. – Vitalizzare May 10 '22 at 16:50
I was asking @Karthik. Trying to solve the #NAME problem. – Darren Bartrup-Cook May 11 '22 at 05:30

score 1 · Answer 2 · answered May 09 '22 at 17:44

The solution proposed by Darren Bartrup-Cook has a serious pitfall: IsDate and CDate functions work with the month names in a current locale. Which means that in general case they do not recognize May 03 and alike as a date

Let's make it work. Here are the assumptions about our data:

There's a pattern in Product Names with _ as a delimiter: the date always comes sixth in a row.
The year is always meant to be the current one.
The name of the month is always indicated in full.

Function ExtractDate(Text As String)
Const Delimiter = "_"
Const Position = 5   ' starting from zero
    ExtractDate = Split(Text, Delimiter)(Position)
End Function
 
Sub Main_Macro()
Dim Source As Range
Dim DateArea As Range
    Set Source = Range(Range("B2"), Range("B2").End(xlDown))
    Set DateArea = Source.Offset(0, -1)
    With DateArea
        .NumberFormat = "[$-409]mmmm d"
        .Formula2R1C1 = "=ExtractDate(RC[1])"
        .Value2 = .Value2
        .NumberFormat = "dd-mm-yyyy"
    End With
End Sub

Here:

"[$-409]mmmm d" force to recognize months in English
.Value2 = .Value2 replace the formula with real data
.NumberFormat = "mm-dd-yyyy" set the date format in a current locale

I have a Product Name like this. I run vba code for extracting date, am not getting the result, it shows error

2 Answers2