Cristen said:
I have two date fields where I need to calculate the difference between the
two dates, however both fields are in text format as shown below. Please
help!
Thu Jan 15 18:03:14 EST 2009
If the format of the date fields is consistent, you could write a
StringToDate function like this:
'----- start of code ------
Function StringToDate(DateString As Variant) As Variant
On Error GoTo Err_Handler
Dim strDateString As String
Dim astrDatePart() As String
strDateString = Trim(DateString & vbNullString)
If Len(strDateString) = 0 Then
StringToDate = Null
Else
astrDatePart = Split(strDateString, " ")
If UBound(astrDatePart) < 5 Then
StringToDate = CVErr(5)
Else
strDateString = _
astrDatePart(1) & " " & _
astrDatePart(2) & " " & _
astrDatePart(5) & " " & _
astrDatePart(3)
StringToDate = CDate(strDateString)
End If
End If
Exit_Point:
Exit Function
Err_Handler:
StringToDate = CVErr(5)
Resume Exit_Point
End Function
'----- end of code ------
The above function will return Null if the DateString argument passed to it
is Null or an empty string, and will return an error if the value does not
have the right number of tokens, or can't be interpreted as a date/time.
I haven't considered how to handle the time-zone token (e.g., "EST"), or
whether it's necessary to try. For this version, I'm just ignoring it.
