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.