CODEWITHSUNDEEP
excelvba
Dio
Dio

Reputation: 3

Extract Date From String, Variable Date Formats

I have looked at so many threads doing similar things, but they usually have one or a couple different date formats they're dealing with. My situation has many different formats and I'm not sure there's a way for me to write something that is all encompassing to capture all of them. I've been making individual scripts for each use case up until now, but it's becoming cumbersome and I'd like a one size fits all solution, if possible.

I am trying to pull the date from the filename of the current workbook and format it as mm-dd-yyyy. Here is non comprehensive list of some of the formats I'm running into:

Sometimes the date is at the beginning, sometimes the middle, sometimes the end. None of it is consistent, so if anyone could help me find some way to bridge this gap, I would be so grateful!

Upvotes: 0

Views: 308

Answers (1)

M. Johnstone
M. Johnstone

Reputation: 112

Using VBA-DotNetLib type library wrapping various .Net Framework classes.

**Note the DotNetLib type library is a work in progress.

Reference Addins required DotNetLib.tlb mscorlib.tlb 2.4

A quick testing parsing DateTime data in the various formats mentioned. To obtain a VBA Date use the ToOADate member which returns a double. To convert use CDate eg. CDate(dateValue.ToOADate) which can be formated in Excel as required.

The example assumes have obtained the date strings to be parsed from the file name and the culture is "en-US". Possibly could use Regular Expressions to extract the date from file name see RegEx and relevant classes. That might be complex as "Sometimes the date is at the beginning, sometimes the middle, sometimes the end."

Public Sub DateTimeTryParseExactFormatsEg()
    'String array of datetime formats to be parsed
    Dim formats() As String
    formats = StringArray.CreateInitialize1D("MMM dd yyyy", "MM_dd_yyyy", _
                            "M.dd.yy", "yyyyMMdd", _
                            "MM -yyyy", "MMMM yyyy")
                            
    'Sample date data to parse
    Dim dateStrings() As String
    dateStrings = StringArray.CreateInitialize1D("JAN 09 1995", "01_09_1995", _
                                "1.09.95", "19950109", _
                                "01 -1995", "January 1995")

    Dim dateValue As DotNetLib.DateTime
    Dim dateString As Variant
    For Each dateString In dateStrings
        If (DateTime.TryParseExact2(dateString, formats, _
                                    CultureInfo.CreateFromName("en-US"), _
                                    DateTimeStyles.DateTimeStyles_None, _
                                    dateValue)) Then
            'Displays a DateTime object formatted as "MM-dd-yyyy"
            Debug.Print VBString.Format("Converted '{0}' to {1}.", dateString, dateValue.ToString2("MM-dd-yyyy"))
            'Displays DateTime object converted to a VBA date and formatted as "MM-dd-yyyy"
            Debug.Print VBString.Format("Converted '{0}' to {1:MM-dd-yyyy}.", dateString, VBA.CDate(dateValue.ToOADate))
        Else
            Debug.Print VBString.Format("Unable to convert '{0}' to a date.", dateString)
        End If
    Next
End Sub

' Output
'    Converted 'JAN 09 1995' to 01-09-1995.
'    Converted 'JAN 09 1995' to 01-09-1995.
'    Converted '01_09_1995' to 01-09-1995.
'    Converted '01_09_1995' to 01-09-1995.
'    Converted '1.09.95' to 01-09-1995.
'    Converted '1.09.95' to 01-09-1995.
'    Converted '19950109' to 01-09-1995.
'    Converted '19950109' to 01-09-1995.
'    Converted '01 -1995' to 01-01-1995.
'    Converted '01 -1995' to 01-01-1995.
'    Converted 'January 1995' to 01-01-1995.
'    Converted 'January 1995' to 01-01-1995.

Upvotes: 0

Related Questions