Error With Results Converting Text Date To Number

[Ark68]

I found this function through a Google search hoping to help me convert a text date (May 6, 2020) to a number that Excel can use.

Public Function GetDate1(ByVal rng As Range)
    GetDate = CDate(Left$(rng.Text, InStr(InStr(rng.Text, ", ") + 1, rng.Text, ", ") - 1))
End Function

With my example in cell A1 of my worksheet, the result I get when I enter (into another cell on the worksheet) =GetDate1(A1) is #VALUE!

Now I don't doubt it's an effective function, but I don't think I'm using it correctly. Unsure the purpose of the $ in the forumal. I suspect it needs to be tweaked somehow to recognize my value.
Suggestions?

 

[Joe4]

You don't need a function for that.
Just use:
=DATEVALUE(A1)
and format the result in your desired date format.

 

[Ark68]

Hmmm. My next question ... who feels silly now? Thank you very much Joe. I guess I assumed I couldn't use DateValue in VBA so I didn't give it any consideration.

 

[Joe4]

There is no need for VBA at all, in this case, unless you are trying to use it in other VBA code.
Even so, DateValue is also a valid VBA function in this case.

 

[Ark68]

So, if I may ask to take this to how I'm looking to apply this ...

I have a worksheet with close to 22000 rows. Column A has the text date value, which will be useless to me moving forward. So, as part of my VBA project, insert a blank row (B) into my worksheet to accept the values of the DateValue calculations leaving me with useable date values. With that, I can delete column A (the text values).

I can use a loop and apply the formula to each cell in B as it relates to the values in A, but when I eliminate column A, the values in B will be errors. Besides, the loop would be pretty slow.

I'm looking for a solution that can place the values a result of the DateValue formula to the cells in B. The range of cells in B will be just the result, not the actual formula.

 

[mikerickson]

Rather than a macro, have you tried using TextToColumns with Date selected on the last menu window.

 

[Ark68]

Hi Mike, I think I kind of know what you're suggesting. But the data is coming in as an Excel File from another application. I have a VBA project that has to manipulate a whole bunch of different aspect of the import. I don't want to have to manipulate the file before using it my application.

I have tried this as an option ... not sure if it is a reliable method though. If I am interpreting it correctly, I won't need to create a new column.

        With wb_rmr
            Set rngData = Worksheets("CORE_DATA").Range("A2:A" & cnt_rec)
            rngData = Format(Evaluate(DateValue(rngData.Address)), "dd-mmm-yy")
        End With

I get an error though. "Type mismatch." with the rngData line.

 

[CountTepes]

Judging by the formula, you are looking for a date which has 2 commas. May, 6, 2020 and not May 6, 2020. It is the structure of your original data which is causing you the #VALUE! error.
So you need to get rid of the first comma search in Instr
Also the formula will not return a value as you have GetDate= and not GetDate1=

So the formula now looks:

Public Function GetDate1(ByVal rng As Range)
    GetDate1 = CDate(Left$(rng.Text, InStr(InStr(rng.Text, " ") + 1, rng.Text, ", ") - 1))
End Function

When you put a date value into a cell, you only need to have the cell formatted correctly to display the date correctly. With general formatting, Datevalue(May 6, 202) returns 43957 into the cell. When formatted to display date it shows 06/05/2020, or whatever locale you have set up.

The $ in left$ is a legacy from Basic when all string functions had a $ sign. Instr used to be Instr$

Also, just copy the formula down into all 22000 cells. The simple way of doing this is:
Put the =getdate1(A1) in B1
Go to column A and hit Ctrl-DownArrow. That will take you to the last entry.
Move across to the left int column B
Hit Ctrl-Shift-Uparrow. That will then have you selected from B22000 to B1.
Hit Ctrl-D to copy down.

Make sure that Column B is formatted as date.
If you want to get rid of column A and leave column B as valid dates. First select column B the Ctrl-C to copy and then select paste special, values only.
That will remove the formulae from column B but retain the data.
Delete column A

 

[Fluff]

If you record a macro using the Text to Columns conversion, you will get

Sub Macro2()
    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
End Sub

Which should do what you need.

 

[Ark68]

CountTepes, thank you for your continued support in an effort to help me overcome my hurdle. I haven't tried your solution as Fluff's was a good fit. It may not have been clear in my OP but I wanted to avoid any manual interaction with the file being imported (by my application) from another source. I was wanting my application to do it all.

Fluff, (via Mike) this is amazing option. I was unaware TextToCould be used in this manner, but it works like a charm. It does appear though that it's another function in which the workbook has to be visible.

Thank you all.