Extract Date From String, Variable Date Formats

Question

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:

MMM dd yyyy
mm_dd_yyyy
m.dd.yy
yyyymmdd
mm-yyyy
MMMM yyyy

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!

"I'm not sure there's a way..." This intuition is correct. If you can't constrain what's allowed to get into the data on the front end, it's not possible to infer the correct way to parse a date value with perfect accuracy later on, because there are a number of formats in common use that are ambigous, with multiple possible reasonable interpretations. A human can generally handle this based on context, but the machine will either not have or not be able to fully make use of these context clues. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 1, 2024 at 16:09
Many years ago I was part of a project to import data that included freetext dates in the middle of lines of text. We ended up building an app to show raw records to live data entry temp workers. To speed entry, we also showed the temps our "best guess" so they could accept it with a single click. We quickly found this didn't work. Because the machine accuracy was still close to 96%, they started just accepting everything, when the whole point was to improve on that 4% error rate. We ended up randomly putting in plausible-but-wrong values about 12% of the time, so they'd keep paying attention. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 1, 2024 at 16:54
Suggest you provide a comprehensive list of what the file names might look like for further assistance. How accurately this can be done is dependent on that. Might be trivial, might be impossible. — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 1, 2024 at 16:55

M. Johnstone · Accepted Answer · 2024-02-02 18:39:52Z

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.

Collectives™ on Stack Overflow

Extract Date From String, Variable Date Formats

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related