Converting Strings or Numbers to Real Dates

The world of data isn’t perfect, that’s for sure, and data can come to your application in many formats. Dates are particularly troublesome because there are so many ways to display and format them. If you routinely need to gather information from outside sources, you’ll appreciate the two functions in this section. The first, dhCNumDate (Listing 2.18), attempts to convert dates stored in numeric values into true Date format. The second function, dhCStrDate (Listing 2.19), performs the same sort of task, but with formatted strings as input.

Some computer systems, for example, store dates as integers such as 19971231 (representing #12/31/1997#) or 52259 (representing #5/22/1959#). The code in dhCNumDate can convert those values into real VBA date/time format, as long as you tell it the layout of the number coming in. For example, to perform the first conversion, you might use

dtmBirthday = dhCNumDate(19971231, "YYYYMMDD")

The function, knowing how the date number was laid out, could pull out the various pieces.

The dhCStrDate function does similar work, but with string values as its input. For example, if all the dates coming in from your mainframe computer were in the format “MMDDYYYY”, you could use

' strOldDate contains "05221959"
dtmNewDate = dhCStrDate(strOldDate, "MMDDYYYY")

to convert the string into a real date.

Listing 2.18: Convert Formatted Numbers to Real Dates

Function dhCNumdate(ByVal lngdate As Long, _
 ByVal strFormat As String) As Variant
    ' Convert numbers to dates, depending on the specified format
    ' and the incoming number. In this case, the number and the
    ' format must match, or the output will be useless.
    Dim intYear As Integer
    Dim intMonth As Integer
    Dim intDay As Integer
    Dim fOK As Boolean
    fOK = True
    Select Case strFormat
        Case "MMDDYY"
            intYear = lngdate Mod 100
            intMonth = lngdate \ 10000
            intDay = (lngdate \ 100) Mod 100
        Case "MMDDYYYY"
            intYear = lngdate Mod 10000
            intMonth = lngdate \ 1000000
            intDay = (lngdate \ 10000) Mod 100
        Case "DDMMYY"
            intYear = lngdate Mod 100
            intMonth = (lngdate \ 100) Mod 100
            intDay = lngdate \ 10000
        Case "DDMMYYYY"
            intYear = lngdate Mod 10000
            intMonth = (lngdate \ 10000) Mod 100
            intDay = lngdate \ 1000000
        Case "YYMMDD", "YYYYMMDD"
            intYear = lngdate \ 10000
            intMonth = (lngdate \ 100) Mod 100
            intDay = lngdate Mod 100
        Case Else
            fOK = False
    End Select
    If fOK Then
        dhCNumdate = DateSerial(intYear, intMonth, intDay)
    Else
        dhCNumdate = Null
    End If
End Function

You’ll find an interesting code technique in dhCNumDate. Given a number like 220459 (#4/22/59# in date format), retrieving the month portion requires some effort. The code accomplishes this by first using integer division (the \ operator), resulting in 2204. Then, to retrieve just the month portion, the code uses the Mod operator to find the remainder you get when you divide 2204 by 100. You’ll find the integer division and the Mod operator useful if you want to retrieve specific digits from a number, as we did in dhCNumDate.

Listing 2.19: Convert Formatted Strings to Real Dates

Function dhCStrdate(strDate As String, Optional _
 strFormat As String = "") As Date
    ' Given a string containing a date value, and a format
    ' string describing the information in the date string,
    ' convert the string into a real date value.
    Dim strYear As String
    Dim strMonth As String
    Dim strDay As String
    Dim fDone As Boolean

    Select Case strFormat
        Case "MMDDYY", "MMDDYYYY"
            strYear = Mid(strDate, 5)
            strMonth = Left(strDate, 2)
            strDay = Mid(strDate, 3, 2)
        Case "DDMMYY", "DDMMYYYY"
            strYear = Mid(strDate, 5)
            strMonth = Mid(strDate, 3, 2)
            strDay = Left(strDate, 2)
        Case "YYMMDD"
            strYear = Left(strDate, 2)
            strMonth = Mid(strDate, 3, 2)
            strDay = Right(strDate, 2)
        Case "YYYYMMDD"
            strYear = Left(strDate, 4)
            strMonth = Mid(strDate, 5, 2)
            strDay = Right(strDate, 2)
        Case "DD/MM/YY", "DD/MM/YYYY"
            strYear = Mid(strDate, 7)
            strMonth = Mid(strDate, 4, 2)
            strDay = Left(strDate, 2)
        Case "YY/MM/DD"
            strYear = Left(strDate, 2)
            strMonth = Mid(strDate, 4, 2)
            strDay = Right(strDate, 2)
        Case "YYYY/MM/DD"
            strYear = Left(strDate, 4)
            strMonth = Mid(strDate, 6, 2)
            strDay = Right(strDate, 2)
        Case Else
            ' If none of the other formats were matched,
            ' just count on Cdate to do the conversion. It may fail,
            ' but we can't help out here.
            dhCStrdate = CDate(strDate)
            fDone = True
    End Select
    If Not fDone Then
        dhCStrdate = DateSerial(Val(strYear), Val(strMonth), _
         Val(strDay))
    End If
End Function

There’s no doubt about it—the code in both these functions relies on brute force. Given the examples already in the functions, you should find it to easy add your own new formats, should the need arise. In each case, it’s just a matter of using the correct mathematical or string functions to perform the necessary conversions.