Convert string to datetime in Excel with individual format parameter (like TO_DATE, DateTime.ParseExact)?

Question

Is there a function that converts a string to datetime using datetime format that is given as parameter in Excel?

You can imagine this like functions below in different platforms:

PLSQL: TO_DATE("20191301","YYYYDDMM")
C#: DateTime.ParseExact("20191301","YYYYDDMM", null)

In what sense is a function which takes input of specific types and returns a specific output a *generic* function? I suspect that you are not using "generic" with its standard meaning. See: https://en.wikipedia.org/wiki/Generic_function . In context, I suspect that you mean *built-in*. — John Coleman, Oct 27 '18 at 12:03
Just `=VALUE()` or =`DATEVALUE()` as a worksheet formulae do exactly this. You don't need VBA. — Dan, Oct 29 '18 at 11:26
I think these functions only can convert if the date string has a pre-defined format defined in the built-in function. Can I convert `29.10.2018:17:48:10` to a datetime? — Ali Tor, Oct 29 '18 at 14:48
I think the closest you can get to this kind of flexibility without VBA would be using the [text to columns feature under the Data tab](https://i.stack.imgur.com/ePBbb.png) (though it will not work on `29.10.2018:17:48:10` because of the time part, but it will work if you first remove it). Where you can convert a entire column of strings (or numbers) to a date value at the [third step from the provided options](https://i.stack.imgur.com/9bAGO.png). — Jerry, Feb 28 '19 at 18:07
Why is there no sample data here for anyone to test? Just testing one string is not a good indicator of a valid solution. Please [edit] your question to include inputs/outputs. **Help us help you** — urdearboy, Mar 01 '19 at 00:28

Asger · Accepted Answer · 2019-03-01T08:54:46.090

Here is an attempt for a user-defined worksheet function:

Function returns datetime value
Supported format-string parts: yyyy, yy, mmm, MM, dd, hh, mm, ss
am, a.m., a. m., pm, p.m., p. m. and their uppercases are all equivalent
Only MM (month) and mm (minute) are case sensitive,
MMM or mmm equals to Jan, Feb, ...
2-digit years below 80 are 20xx, 80 or above are 19xx
Format-string must have the same length as the source string,
otherwise returns #N/A error
Filling characters can be blank, colon, double colon or else

Examples:

EDIT:

Public Function TO_DATE(ByRef src As String, ByRef frmt As String) As Variant
    Dim y As Long, m As Long, d As Long, h As Long, min As Long, s As Long
    Dim am As Boolean, pm As Boolean
    Dim pos As Long

    If Len(src) <> Len(frmt) Then
        TO_DATE = CVErr(xlErrNA)  ' #N/A error
        Exit Function
    End If

    pos = InStr(1, frmt, "yyyy", vbTextCompare)
    If pos > 0 Then
        y = Val(Mid(src, pos, 4))
    Else: pos = InStr(1, frmt, "yy", vbTextCompare)
        If pos > 0 Then
            y = Val(Mid(src, pos, 2))
            If y < 80 Then y = y + 2000 Else y = y + 1900
        End If
    End If

    pos = InStr(1, frmt, "mmm", vbTextCompare)
    If pos > 0 Then
        m = month(DateValue("01 " & (Mid(src, pos, 3)) & " 2000"))
    Else: pos = InStr(1, frmt, "MM", vbBinaryCompare)
        If pos > 0 Then m = Val(Mid(src, pos, 2))
    End If

    pos = InStr(1, frmt, "dd", vbTextCompare)
    If pos > 0 Then d = Val(Mid(src, pos, 2))

    pos = InStr(1, frmt, "hh", vbTextCompare)
    If pos > 0 Then h = Val(Mid(src, pos, 2))
    If InStr(1, src, "am", vbTextCompare) > 0 Then am = True
    If InStr(1, src, "a.m.", vbTextCompare) > 0 Then am = True
    If InStr(1, src, "a. m.", vbTextCompare) > 0 Then am = True
    If InStr(1, src, "pm", vbTextCompare) > 0 Then pm = True
    If InStr(1, src, "p.m.", vbTextCompare) > 0 Then pm = True
    If InStr(1, src, "p. m.", vbTextCompare) > 0 Then pm = True
    If am And h = 12 Then h = 0
    If pm And h <> 12 Then h = h + 12

    pos = InStr(1, frmt, "mm", vbBinaryCompare)
    If pos > 0 Then min = Val(Mid(src, pos, 2))

    pos = InStr(1, frmt, "ss", vbTextCompare)
    If pos > 0 Then s = Val(Mid(src, pos, 2))

    TO_DATE = DateSerial(y, m, d) + TimeSerial(h, min, s)
End Function

Nice and simple parsing method. Kudos for keep the code short. — Ahmed AU, Feb 28 '19 at 18:52
That's what I am looking for. Thanks a lot @Asger. Btw how can I add AM/PM capability to this code or can you update with this ability? — Ali Tor, Mar 01 '19 at 07:02
This is a great function. It even works for date string like "Wed, 29 Jan 2020 07:00:55 +0000": TO_DATE(strDate, "Day, DD MMM YYYY hh:mm:ss +0000") — Vlado, Jan 29 '20 at 18:05

score -1 · Answer 2 · answered Oct 27 '18 at 12:05

-1

May be this function returns result as you wish (you can modify to suit)

Function Convert2Date(s) As Date
Dim a           As Variant

a = Split(s, "/")
Convert2Date = DateSerial(a(2), a(1), a(0))
End Function

answered Oct 27 '18 at 12:05

YasserKhalil

9,138
7
36
95

2

VBA is completely unnecessary here when there are built-in worksheet functions that will do this. Also, your function is assuming a format for the string, it would make more sense to be able to pass in the formatting string to specify the datatime format – Dan Oct 29 '18 at 11:28

score -3 · Answer 3 · answered Feb 28 '19 at 15:12

-3

Based on the string you gave

Cell A1

29.10.2018:17:48:10

Use this

=DATEVALUE(SUBSTITUTE(LEFT(A1,FIND(":",A1)-1),".","/"))+TIMEVALUE(MID(A1,FIND(":",A1)+1,20))

answered Feb 28 '19 at 15:12

xn1

417
4
12

Your solution only works with the example "29.10.2018:17:48:10" from the comments, but not with the example "20191301" from the question. – Asger Feb 28 '19 at 18:31
Correct but op was concerned with date and time, those function examples were date only and. The only string op explicitly defined that had date and time was the one in the comments. – xn1 Feb 28 '19 at 18:44

Convert string to datetime in Excel with individual format parameter (like TO_DATE, DateTime.ParseExact)?

3 Answers3