Locale agnostic text-to-date conversion function

Question

I'm trying to convert a string that looks like "MMM DD YYYY" (e.g. "Jan 17 2015") into Excel serial date.
The problem is: I'm running an Int'l version of Windows and neither DATEVALUE nor CDate() recognize this format (they depend on system locale). Moreover, when I'll write the function, I won't know on which locale it will be used.
This post: Excel VBA - Convert Text to Date? gives a great solution (invoking TextToColumns programatically), but it's of no use to me - I need a function (or an UDF) that will process a string inside a formula. Those "dates" are the result of yet another calculation (regexps, actually), so I can't use TextToColumns on those cells - it'll mess them up.

Yes, I can write an UDF with a static lookup table that will feed numbers into DateSerial(), but maybe there's a better way?

CLARIFICATION This is not about fixing order of d/m/y. The main culprit is the textual name of a month - it's in English so int'l version of Excel won't recognize it. For example, February is "helmikuuta" in Finnish locale and "luty" in Polish one :)

ON ANSWERS Wow so many useful answers! My (short) experience with using SO has been rather negative so far - I ask a question, nobody says anything constructive and in the end I develop a solution and answer the question myself. This time, however, I am pleasantly surprised and my faith in humanity is restored :) Will review the answers and comment on them now...

Answer 1

Here's a simple UDF,

Function repairDate(str As String)
    Dim arr As Variant, mnths As Variant

    mnths = Array("Jan", "Feb", "Mar", _
                  "Apr", "May", "Jun", _
                  "Jul", "Aug", "Sep", _
                  "Oct", "Nov", "Dec")
    arr = Split(str, Chr(32))
    repairDate = DateSerial(arr(2), _
                    Application.Match(arr(0), mnths, 0), _
                    arr(1))
End Function

This returns the serial date. Suggest you use one of the date formats starting with a * as they are cross-language support.

Answer 2

Assuming that your original text date is in A1, this formula should give you the desired output:

=DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(A1,3)&" 1")),MID(A1,5,2))

This is how it works:

1) We extract the date parts from the string in this way:

RIGHT(A1,4) gives you the year

MID(A1,5,2) gives you the day

LEFT(A1,3) gives you the month

2) We convert the month to a number MONTH and only DATEVALUE for the month part

3) And finally we pass this values to the DATE function.

Using Datevalue only for the month should work with your international settings.