1

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.

Improve this question
3
  • format("Feb 10, 2016","dd mm yyyy") Commented May 10, 2016 at 12:55
  • Is the existing data actually a date, or a string that looks like a date? Commented May 10, 2016 at 13:13
  • You might want to have a look at the following post for a more elaborate answer: stackoverflow.com/questions/37100821/… Commented May 10, 2016 at 13:13

4 Answers 4

Reset to default
9

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Why not just use CDate(rng) directly?
@arcadeprecinct because you can't call VBA functions from the worksheet - they have to be wrapped in a UDF like this.
Not sure what you mean @arcadeprecinct, or am I missing something? As a UDF I'm not sure you could shorten it any further.
@DarrenBartrup-Cook I didn't think of calling it from a worksheet so I thought there was no need for a UDF
2

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

Improve this answer

Comments

2

Try this:

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

This will give: 2

Improve this answer

Comments

2

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

Improve this answer

3 Comments

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")
@DarrenBartrup-Cook It will depend on your regional settings. In the UK that format wouldn't be recognised.
ah, ok. Cheers Rory - that'll be why it wasn't recognised then.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.