Excel VBA - Convert Date String to Date

Question

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.

Is the existing data _actually_ a date, or a string that looks like a date? — SierraOscar, May 10 '16 at 13:13
You might want to have a look at the following post for a more elaborate answer: http://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 — Ralph, May 10 '16 at 13:13

score 10 · Accepted Answer · answered May 10 '16 at 12:58

10

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

answered May 10 '16 at 12:58

Darren Bartrup-Cook

18,362
1
23
45

2

Why not just use `CDate(rng)` directly? – arcadeprecinct May 10 '16 at 13:04
2

@arcadeprecinct because you can't call VBA functions from the worksheet - they have to be wrapped in a UDF like this. – SierraOscar May 10 '16 at 13:14
Not sure what you mean @arcadeprecinct, or am I missing something? As a UDF I'm not sure you could shorten it any further. – Darren Bartrup-Cook May 10 '16 at 13:14
1

@DarrenBartrup-Cook I didn't think of calling it from a worksheet so I thought there was no need for a UDF – arcadeprecinct May 10 '16 at 16:54

score 3 · Answer 2 · answered May 10 '16 at 12:55

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

score 2 · Answer 3 · answered May 10 '16 at 12:57

2

Try this:

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

This will give: 2

answered May 10 '16 at 12:57

Mrig

11,612
2
13
27

score 2 · Answer 4 · answered May 10 '16 at 13:11

2

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

answered May 10 '16 at 13:11

Robert Co

1,715
8
14

Are you sure of that? I tried it before leaving my answer and it returned a `#VALUE!` error. Admittedly the help file for the function states: _Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999_. Using the format (and date) they've used I get the error with `=DATEVALUE("January 1, 1900")` – Darren Bartrup-Cook May 10 '16 at 13:50
@DarrenBartrup-Cook It will depend on your regional settings. In the UK that format wouldn't be recognised. – Rory May 10 '16 at 14:08
ah, ok. Cheers Rory - that'll be why it wasn't recognised then. – Darren Bartrup-Cook May 10 '16 at 14:13

Excel VBA - Convert Date String to Date

4 Answers4

Linked