Legacy data comes in many formats, but one thing is sure: the date format can almost never be stuffed into a NotesDateTime with good results. Once upon a time I had to convert a lot of data, delivered in text files with fixed length lines, to Notes. There where subtle differences in the date formats (YYYY-DD-MM, YYYYMMDD etc.) so I made an all-purpose function to extract the dates and do something meaningful with them - this one. As arguments it takes a string with a date somewhere in it and another string describing where to find the date and how it's constructed. It returns a NotesDateTime object.
Function ConvertDate(datestring As String, formatstring As String) As NotesDateTime ' Formatstring may contain any of the following: ' y : a part of the year is here ' m : a part of the month is here ' d : a part of the day of the month is here. ' Any other character is ignored. Characters are case-independent. ' Generally the resulting date in the string must be something that can be used ' to construct a NotesDateTime which gives a pretty wide margin. Dim session As New NotesSession Dim international As NotesInternational Dim datesep As String Dim dateday As String Dim datemonth As String Dim dateyear As String Dim dateinfo As Variant Dim counter As Integer Dim maxlen As Integer Dim curchar As String Dim formchar As String Dim tempdatetime As NotesDateTime ' These constants are for convenience and execution speed only. If you want, ' substitute your own values - as long as they are the same that are used in ' the formatstring parameter. Const YEARMODEIND = "y" Const MONTHMODEIND = "m" Const DAYMODEIND = "d" On Error Goto ErrorHandler ' This is a special purpose function for parsing entire strings so the date string ' must be at least as long as the format string. maxlen = Len(formatstring) datestring = Left(datestring & Space(maxlen), maxlen) ' Then we simply go through the format string and pull out the interesting characters ' from the corresponding positions in the date string if we find a formatting character. counter = 1 While counter <= maxlen curchar = Lcase(Mid(datestring, counter, 1)) formchar = Lcase(Mid(formatstring, counter, 1)) Select Case formchar Case YEARMODEIND dateyear = dateyear & curchar Case MONTHMODEIND datemonth = datemonth & curchar Case DAYMODEIND dateday = dateday & curchar Case Else ' NOP. Ignore this character. End Select ' formchar counter = counter + 1 Wend ' counter <= maxlen ' If a part of the date can't be found we can't complete the date and so exit, ' returning Nothing. This is a very simple check but it's not meant to be complete. ' A complete check will be done by the NotesDateTime class when invoked. This only ' makes sure that we don't crash out. If dateyear = "" Or datemonth = "" Or dateday = "" Then Exit Sub ' We've got the parts. We can then combine them into one string with a proper ' date separator and use the string to construct a NotesDateTime. Set international = session.International datesep = international.DateSep Set tempdatetime = New NotesDateTime(dateday & datesep & datemonth & datesep & dateyear) If tempdatetime.DateOnly <> "" Then ' If something went wrong Notes says nothing, not even Nothing. We have to check ' some obscure property on the object. Not elegant... Set ConvertDate = tempdatetime End If ' tempdatetime.DateOnly <> "" ExitSub: Exit Function ErrorHandler: ' Not really handling any errors, just returning Nothing if something goes wrong. Resume ExitSub End Function ' ConvertDate