VBA Function: Extract Date from String - Type Mismatch

Question

I wrote a simple function to extract date from cells. I have some cells containing strings "5-Oct-2018" and other cells containing "2018/10/10 Random Texts". I want to truncate the random texts and also convert the rest of the strings into dates with the same format.

Since the random texts only ever show up when the string is >10 characters or longer, I decided to truncate everything to the right.

I wrote the following, but it keeps getting stuck on this line "FnDateExtract = Format(CDate(RawExtract), "yyyy/mm/dd")" saying Type Mismatch. What am I doing wrong?

Function FnDateExtract(fnFile, fnTab, fnRow, fnColumn) As Date
    Dim RawExtract As String
    With Workbooks(fnFile).Worksheets(fnTab)
        RawExtract = .Cells(fnRow, fnColumn).Text
        If Len(RawExtract) > 10 Then RawExtract = Left(RawExtract, 10)
        FnDateExtract = Format(CDate(RawExtract), "yyyy/mm/dd")
    End With
End Function

Well, what's the value of RawExtract when it throws an error? Also, Format returns a string, and your function returns a date. Either change your function to return a string, or remove the format to 'yyyy/mm/dd'. — Josh Eller
– Josh Eller, Commented Oct 16, 2018 at 13:08
it means the date in the cell can not be converted using CDate. — usmanhaq
– usmanhaq, Commented Oct 16, 2018 at 13:10
as @JoshEller implies, most likely, the ten left-most characters of whatever is in the cell is not something that can be considered a date. Add a breakpoint and see what is there. — Ron Rosenfeld
– Ron Rosenfeld, Commented Oct 16, 2018 at 13:32

Kubie · Accepted Answer · 2018-10-16 13:49:50Z

3

Your function looks fine, just need to make some minor changes:

Function FnDateExtract(fnFile, fnTab, fnRow, fnColumn) As Date
    Dim RawExtract As String
    With Workbooks(fnFile).Worksheets(fnTab)
        RawExtract = Left(CStr(.Cells(fnRow, fnColumn).Text), 10)
        FnDateExtract = CDate(Format(RawExtract, "yyyy/mm/dd"))
    End With
End Function

Moved the CDate() to outside the Format() function in order to set return type to Date

Also please note that you can use InStr(*string*, " ") - 1 instead of 10 with the Left() function to grab the date string. It may be more accurate in certain situations.

edited Oct 16, 2018 at 13:49

answered Oct 16, 2018 at 13:30

Kubie

1,5714 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ron Rosenfeld Over a year ago

Actually, you don't need to test for Len > 10 since the LEFT function will not return more characters than are present.

Kubie Over a year ago

This is true, thank you for the input will adjust answer accordingly @RonRosenfeld

Storax Over a year ago

IMO this will not solve the issue in case the first ten characters cannot be interpreted as a date. You have to use IsDate beforehand in order to check if CDate does not run into an error.

Kubie Over a year ago

@Storax this is based off of the information provided in the question. You can post a more robust solution if you feel it is needed

André Tân Over a year ago

@Kubie thanks! Taking away CDate actually fixes it. Although I am a bit confused. Why is it possible to convert it to the yyyy/mm/dd format simply with Format? How does it recognize which numbers are dates/years/months? Does it just cycle through conventional date formats?

|

Storax · Accepted Answer · 2018-10-16 14:16:59Z

It seems that you have data which cannot converted into dates. That means you have to make your code more robust. A first version could look like that

Function FnDateExtract(fnFile, fnTab, fnRow, fnColumn) As Date
Dim RawExtract As String
    With Workbooks(fnFile).Worksheets(fnTab)
        RawExtract = .Cells(fnRow, fnColumn).Text
        If Len(RawExtract) > 10 Then RawExtract = Left(RawExtract, 10)

        If IsDate(RawExtract) Then
            FnDateExtract = CDate(Format(RawExtract, "yyyy/mm/dd"))
        Else
            FnDateExtract = vbEmpty
        End If

    End With

End Function

In a more improved version one would properly define the parameters maybe like that

Function FnDateExtract(ByVal fnFile As String, ByVal fnTab As String, _
                    ByVal fnRow As Long, ByVal fnColumn As Long) As Date

The function can still easily be disrupted as you assume you have a workbook named fnFile with a sheet fnTab. The easiest (maybe also quick and dirty) way to handle this is to add an error handler.

So the second version could look like that

Function FnDateExtract(ByVal fnFile As String, ByVal fnTab As String, _
                    ByVal fnRow As Long, ByVal fnColumn As Long) As Date
Dim RawExtract As String

On Error GoTo EH

    With Workbooks(fnFile).Worksheets(fnTab)
        RawExtract = .Cells(fnRow, fnColumn).Text
        If Len(RawExtract) > 10 Then RawExtract = Left(RawExtract, 10)
        If IsDate(RawExtract) Then
            FnDateExtract = CDate(Format(RawExtract, "yyyy/mm/dd"))
        Else
            FnDateExtract = vbEmpty
        End If
    End With

    Exit Function

EH:
    FnDateExtract = vbEmpty

End Function

Collectives™ on Stack Overflow

VBA Function: Extract Date from String - Type Mismatch

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related