From previous questions on this and other sites I was given a function that can parse for and find dates in text strings or cells. Here's the function:
Function GetDate(strInput As String) As Date Dim DateFormat() As String Dim intDateLength As Integer Dim intMaxFormat As Integer Dim intFrmtCtr As Integer Dim intPosition As Integer intMaxFormat = 6 ReDim DateFormat(1 To intMaxFormat) DateFormat(1) = "*##[-/]##[/-]####*" DateFormat(2) = "*#[-/]##[-/]####*" DateFormat(3) = "*##[-/]#[-/]####*" DateFormat(4) = "*##[-/]##[-/]##*" DateFormat(5) = "*#[-/]##[-/]##*" DateFormat(6) = "*#[-/]#[-/]##*" GetDate = Now For intFrmtCtr = 1 To intMaxFormat If strInput Like DateFormat(intFrmtCtr) Then intDateLength = Len(DateFormat(intFrmtCtr)) - 8 strInput = Replace(strInput, " ", "") For intPosition = 1 To Len(strInput) If Mid(strInput, intPosition, intDateLength) Like DateFormat(intFrmtCtr) Then GetDate = DateValue(Mid(strInput, intPosition, intDateLength)) Exit Function End If Next intPosition End If Next intFrmtCtr End Function
This is then called by referencing a cell address:
'Gets date from cell A2 Range("A2").Select dateWork = GetDate(Selection)
I can then format the output using
Format(dateWork, "mmddyy") or similar.
In the case above, given a cell contents of
8/31/2011 the string
08312011 is returned.
Beginning with cell content dates with a single digit day (ie, early in the month), though, the function began returning the first two digits of the year instead of the second two digits. All of a sudden, dates started looking like they were in 2020, not 2011.
I've been able to test this using otherwise identical input data sheets where dates up to and including
8/31/2011 were returned correctly and dates beginning with
9/1/2011 and continuing all return "20" as the year.
Where is this function breaking down?
UPDATE clarification for questions:
I use this function in several macros acting upon data exported from a report-generating tool (Business Objects). It is possible to manually edit the date in the input files, but that rather defeats the purpose of the macros and so should not be considered an option.
The GetDate() function can be called upon to find dates in any string. The strings can be just the date in any of various formats, or they can be combinations of text and date such as "Totals as of 9/13/1977".
I would appreciate your simplification or edits to the function so long as it retains the abilities laid out above.