CODEWITHSUNDEEP
excelvba
user5873603
user5873603

Reputation:

Excel VBA - Convert Date String to Date

I have a number of strings in cells which are dates but they need to be converted to date format.

They are in the following format: 

mmm dd, yyyy

For Example: 

Feb 10, 2016

So they can be 11 or 12 in length: 

Feb 1, 2016

I have started writing a function to parse each part of the string individually (day as integer, month as integer and year as integer) to then convert into date format.

Firstly, is there an easier/slicker way to do this than the above?

If there isn't an easier way, what's the best was to convert the 3 letter month (e.g. Feb or Mar or Apr) into a month number (e.g. 2 or 3 or 4)? As that is the only bit I'm really stuck with.

Upvotes: 1

Views: 50339

Answers (4)

Robert Co
Robert Co

Reputation: 1715

Do you really need VBA? Excel provides you with a Text to Date function =DATEVALUE() and it does recognize Feb 10, 2016.

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

In VBA you could use: cdate("Feb 10, 2016").
As a function this would be: 

Public Function ConvertToDate(rng As Range) As Date

    ConvertToDate = CDate(rng)

End Function

Upvotes: 9

Mrig
Mrig

Reputation: 11712

Try this:

Sub test()
    MsgBox Month(Datevalue("Feb 10, 2016"))
End Sub

This will give: 2

Upvotes: 2

John Coleman
John Coleman

Reputation: 52008

Use a Date variable to read the value and then use the Format function to format the date as you see fit.

For example:

Sub test()
    Dim D As Date
    D = Range("A1").Value
    MsgBox Format(D, "mm/dd/yyyy")
End Sub

If I run the code with the string "Feb 10, 2016" in A1 then "02/10/2016" is displayed in the message box

Upvotes: 2

Related Questions