CODEWITHSUNDEEP
excelvba
capm
capm

Reputation: 1047

Excel VBA get date from a string

If I have a cell with the following:

Tuesday, April 16th 2009

How do I convert that string into a date format recognized by Excel. I think I have to use MID() and FIND() functions.

Upvotes: 2

Views: 10006

Answers (2)

ccampj
ccampj

Reputation: 725

Here is a function that would work from VBA and as a User Defined Function 

Function GetDate(InString As Range) As Date
    Dim newDate As Date
    Dim tmpDate As String

    'Sample Date
    'Tuesday, April 16th 2009

    'Remove the Day of the Week.
    tmpDate = Trim(Mid(InString, InStr(InString, ",") + 1))

    'Get rid of "th"
    tmpDate = Replace(tmpDate, "th ", " ")

    'Get rid of "rd"
    tmpDate = Replace(tmpDate, "rd ", " ")

    'Get rid of "nd"
    tmpDate = Replace(tmpDate, "nd ", " ")

    'Get rid of "st"
    tmpDate = Replace(tmpDate, "st ", " ")

    'Convert string to date
    newDate = DateValue(tmpDate)

    GetDate = newDate
End Function

Upvotes: 5

SeanC
SeanC

Reputation: 15923

Assuming that text is in A1, we can break it out into separate parts, and use DATEVALUE to put it together.

B1: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))
B2: =IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))
B3: =RIGHT(A1,4)
B4: =DATEVALUE(B2&" "&B1&" "&B3)

Or, you can do it in one go:

=DATEVALUE(IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))&" "&RIGHT(A1,4))

Upvotes: 0

Related Questions