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
    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.


Your Mid(strInput, intPosition, intDateLength) isn't working for you. For 9/1/2011, you're matching DateFormat(6) = "*#[-/]#[-/]##*". So Mid(strInput, intPosition, intDateLength) = Mid("9/1/2011", 1, 6) and returns "9/1/20" rather than "9/1/2011".

based on your example above, I think you need to add 2 more patterns so that you cover all combinations of 1 and 2 digit months/days & 2 and 4 digit years. Order is important because you want to match the 4 digit years first (noting that the reason you have a problem is that #/#/## matches 9/1/2011 when in fact you only want it to match 9/1/11. So the code change is something like the below. I haven't exhaustively tested it with all date formats, but it seems OK.

intMaxFormat = 8
ReDim DateFormat(1 To intMaxFormat)

DateFormat(1) = "*##[-/]##[/-]####*"
DateFormat(2) = "*#[-/]##[-/]####*"
DateFormat(3) = "*##[-/]#[-/]####*"
DateFormat(4) = "*#[-/]#[-/]####*"
DateFormat(5) = "*##[-/]##[-/]##*"
DateFormat(6) = "*##[-/]#[-/]##*"
DateFormat(7) = "*#[-/]##[-/]##*"
DateFormat(8) = "*#[-/]#[-/]##*"
  • Is that due to the single-digit day? – music2myear Sep 12 '11 at 21:48
  • Yes. DateFormat(6) is effectively m/d/yy, which is the only one that 9/1/2011 matches. The other formats all require a 2 digit day or month. So if you put in 09/01/2011, it would work fine – Rhys Gibson Sep 12 '11 at 21:53
  • 1
    If you can tell me what you're trying to achieve, I can simplify the function for you. I think there's some redundant bits in it, especially if all your dates are the same format. – Rhys Gibson Sep 12 '11 at 22:56
  • I've added clarification regarding what sort of strings GetDate() works upon and it's requirements per my usage. – music2myear Sep 13 '11 at 14:22
  • Looking at the DateFormats you've added, should numbers 6 and 7 be reversed? Numbers 2 and 3 go in what appear to be the opposite order from 6 and 7. Which is correct (or should they be in the order you've given them)? – music2myear Oct 4 '11 at 19:23

I've written a shorter macro that should do what you want. It splits the string up into an array by the space character. The macro then runs through the array looking for the date. If there are extra characters attached to the date it will fail. However, if the date is its "own word" separated by spaced on both ends it works perfectly. Depending on your needs it may need to be modified a little bit.

Function GetDate(strInput As String) As Date

 Dim strDate() As String, i As Long

  strDate = Split(strInput, " ")

 For i = 0 To UBound(strDate) - 1
    If Right(strDate(i), 1) = "." Then strDate(i) = Left(strDate(i), Len(strDate(i)) - 1)
    If IsDate(strDate(i)) Then
       GetDate = strDate(i)
       Exit For
    End If
 Next i

End Function
  • It looks like that handles dates followed by periods, right? But other characters such as commas and other punctuation may cause problems? – music2myear Sep 13 '11 at 15:14
  • Yup. In my simple test I had a period. You could test each string for a number at the end and delete if it is not. Without knowing his exact requirements it is impossible to write code effectively. – wbeard52 Sep 13 '11 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.