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...

If you `Split()` the string you could easily reconstruct the date in a familiar format. Like DD-MMM YYYY which `CDate()` should recognize. — Miguel_Ryu, Feb 08 '18 at 11:25
You can use Excel built-in functions (such as `Left()`,`Right()`,`Mid()`) to put date values into correct order. — AntiDrondert, Feb 08 '18 at 11:27
Thanks guys but unfortunately it won't work (see "Clarification" above) :( — Alexander, Feb 08 '18 at 12:13

score 4 · Accepted Answer · answered Feb 08 '18 at 11:39

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.

this was exactly what I needed :) Although I had thought that there could be another way, without writing an UDF. I like your clean, elegant and simple approach and hadn't thought about using Application.Match() for lookup tables. Neat trick :) — Alexander, Feb 08 '18 at 14:37

score 3 · Answer 2 · answered Feb 08 '18 at 11:33

3

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.

answered Feb 08 '18 at 11:33

J_P

761
8
17

Thanks J_P, but unfortunately it won't work (see "Clarification" in my question) :( – Alexander Feb 08 '18 at 12:14
Upvoting this anyway as you might have been misled by my question. Thanks for taking time to answer! – Alexander Feb 08 '18 at 12:17
Thank you! better change your face from :( to :) as the clarification wasn't in your original question and you added it to the question later, after my answer. :) – J_P Mar 20 '18 at 13:14

score 3 · Answer 3 · answered Feb 08 '18 at 11:39

3

If you need an UDF, maybe this could help

Public Function CONVERT_DATE(ByRef rng As Range) As Date

Dim MyDate As Variant
MyDate = Split(rng.Value, " ")


'month is always mydate(0). So we use select case
Select Case UCase(MyDate(0))
    Case "JAN"
        MyDate(0) = 1
    Case "FEB"
        MyDate(0) = 2
    Case "MAR"
        MyDate(0) = 3
    Case "APR"
        MyDate(0) = 4
    Case "MAY"
        MyDate(0) = 5
    Case "JUN"
        MyDate(0) = 6
    Case "JUL"
        MyDate(0) = 7
    Case "AUG"
        MyDate(0) = 8
    Case "SEP"
        MyDate(0) = 9
    Case "OCT"
        MyDate(0) = 10
    Case "NOV"
        MyDate(0) = 11
    Case "DEC"
        MyDate(0) = 12
End Select

'mydate(1) is day
'mydate(2) is year

CONVERT_DATE = CDate(Format(Join(MyDate, "/"), "dd/mm/yyyy"))

End Function

This only will work if your data is always like

"Jan 17 2015"

answered Feb 08 '18 at 11:39

Foxfire And Burns And Burns

10,665
2
17
38

Thanks @Foxfire; unfortunately that final CDate() call would fail as it expects that my locale takes date in dd/mm/yyy format (and we can't be sure of that)... DateSerial() would work in this case. Upvoting anyway, thanks for your time and effort :) – Alexander Feb 08 '18 at 14:34
1

DIY Programming is a bit like DIY brain-surgery. :-) If you ARE going to write code that only solves a very limited set of situations, why take 24 lines to write what you can write in 1 ? eg MyDate(0) = InStr("...JAN/FEB/MAR/APR/MAY/JUN/JUL/AUG/SEP/OCT/NOV/DEC", UCase(MyDate(0)) / 4 – Warren K Sep 21 '18 at 12:09
@WarrenK, that's EXACTLY what I needed! This should be the accepted answer :) Succint, clear and does the job. Pythonian even. Shame that I'm doomed to the VBA (for now), I miss "propah" languages... – Alexander Jan 10 '19 at 10:59

score 1 · Answer 4 · answered Feb 08 '18 at 11:58

Given your dates are in column A starting from 5th row:

Sub CorrectDateArray ()
    Dim MySheet As Worksheet, DateRange As Range
    Dim lRow As Long, vArr(), i As Integer
    '    Designate your worksheet, where MySheet is your actual name
    Set MySheet = Thisworkbook.Worksheets("MySheet")
    '    Last row
    lRow = MySheet.Range("A" & MySheet.Rows.Count).End(xlUp).Row
    '    Designate DateRange
    Set DateRange = MySheet.Range("A5:A" & lRow)
    '    Put values of array formula into array
    vArr = Evaluate("=LEFT(REPLACE(" & DateRange.Address & ",1," & _
                        "FIND(" "," & DateRange.Address & ",1),"")," & _
                            "FIND(" ";REPLACE(" & DateRange.Address & ",1," & _
                                "FIND(" ",N2,1),""),1)) & " & _
                    "LEFT(" & DateRange.Address & ";FIND(" "," & DateRange.Address & ",1)) & " & _
                    "RIGHT(" & DateRange.Address & ",4)")
    '    Finaly, a correct array of dates
    For i = Lbound(vArr) To Ubound(vArr)
        vArr(i) = CDate(vArr(i))
    Next
End Sub

Thanks AntiDrondert, but unfortunately it won't work (see "Clarification" in my question) :( Upvoting this anyway as you might have been misled by my question. Thanks for taking time to answer! — Alexander, Feb 08 '18 at 12:18

Locale agnostic text-to-date conversion function

4 Answers4