How to specify a format when converting String to Date

Question

I know I can use Format() when converting a date into a string. And Cells.NumberFormat to change the display text of a date.

I'm trying to go the opposite direction. I have a string like "231211" or "08AUG11" and a corresponding date format like "YYMMDD" or "DDMMMYY" and I just need to convert the string based on the format. I already have a way to find the correct format of each string, I just need to do the conversion.

I can't seem to find the right function/method to do what I'm asking. DateTime.ParseExact doesn't seem to exist in Excel 2007, even after I added mscorlib.dll as a reference.

Is there an alternative I can use? Or how do I get DateTime.ParseExact to work properly in Excel 2007?

Since the string is not always the same length and there are about 30 different formats, it's quite annoying to conditionally split the string and interpret the substrings individually. I would love to be able to just parse the date based on the format.

DateTime.ParseExact is .net not vba. There is no easy way around the parsing that I know of. — Scott Craner
– Scott Craner, Commented Nov 12, 2020 at 23:33
Does this answer your question? Convert String to Excel Date and Time, with specific string format — computercarguy
– computercarguy, Commented Nov 12, 2020 at 23:42
@computercarguy no that would not answer this, as that is for one specific string format, as the OP stated they have about 30 different formats to parse. — Scott Craner
– Scott Craner, Commented Nov 12, 2020 at 23:43
The format is immaterial. In order to read the date you need to know where it keeps its day, month and year. You can call "ddmmmyy" Formats(1) and "yymmdd" Formats(2) and write code to parse a date by either pattern. Deriving that pattern from a date format just is a method of knowing the pattern number. It's the patten ID that will solve your problem, not the method by which you find it. — Variatus
– Variatus, Commented Nov 12, 2020 at 23:49

Scott Craner · Accepted Answer · 2020-11-12 23:55:46Z

4

here is a start to create your own function:

Function dateParser(str As String, fmt As String) As Date
    If Len(str) <> Len(fmt) Then Exit Function
    
    Dim dy As Long
    Dim mnth As String
    Dim yr As Long
    Dim mnthnm As Long
    
    Dim i As Long
    For i = 1 To Len(str)
        If UCase(Mid(fmt, i, 1)) = "D" Then
            dy = dy * 10 + CLng(Mid(str, i, 1))
        ElseIf UCase(Mid(fmt, i, 1)) = "Y" Then
            yr = yr * 10 + CLng(Mid(str, i, 1))
        ElseIf UCase(Mid(fmt, i, 1)) = "M" Then
            mnth = mnth & Mid(str, i, 1)
        End If
    Next i
    
    If IsNumeric(mnth) Then
        mnthnm = CLng(mnth)
    Else
        mnthnm = Month(CDate("01 " & mnth & " 2020"))
    End If
    
        
     dateParser = DateSerial(yr, mnthnm, dy)
    
    
End Function

Used like:

Sub test()
    Dim str As String
    str = "08AUG11"
    
    Dim fmt As String
    fmt = "DDMMMYY"
    
    Dim x As Date
    x = dateParser(str, fmt)
    
    debug.print x
End Sub

answered Nov 12, 2020 at 23:55

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Toddleson Over a year ago

This is what I was going to fall back on if there was no equivalent to DateTime.ParseExact. But yours is much nicer than what I was planning. Essentially splitting the string but I like this idea of doing it character by character.

Toddleson Over a year ago

I wish I could give this a second upvote. I had the exact same problem in another project, and your answer solved it a second time for me.

Collectives™ on Stack Overflow

How to specify a format when converting String to Date

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related