change number string to date format 1

[v1vek]

1.In Excel I would like to enter a number string say 01011998122320 and convert it into a date time format i.e.
01/01/1998 12:23:20 i.e dd/mm/yyyy hh:mm:ss
where incorrect or invalid values would return error message and correct values return the excel entry in date/time format
2.if date and time were entered in different columns as a number string ddmmyyyy and hhmmss or hhmm is it possible to combine the two in a third column as dd/mm/yyyy hh:mm:ss or if ss is not specified to take the value as dd/mm/yyyy hh:mm:00
3. This is to basically calculate time intervals between two specified time entries and to facilitate date entry. I would be using this to do some survival analysis in cancer patients
All help appreciated

 

[Zathras]

Here is one way: (The TEST sub is for demo only.)

===================================================

Option Explicit

Sub TEST()
Dim bOk As Boolean
Dim dtDate As Date
  bOk = ParseDate("01011998122320", dtDate)
  If bOk Then
    MsgBox dtDate
  Else
    MsgBox "Invalid Date (2nd notice)"
  End If
End Sub

Function ParseDate(DateString As String, _
                 DateVal As Date) As Boolean
' Expects a 14-character string "ddmmyyyyhhmmss"
Dim Day As Integer
Dim Month As Integer
Dim Year As Integer
Dim Hour As Integer
Dim Minute As Integer
Dim Second As Integer

Day = Mid(DateString, 1, 2)
Month = Mid(DateString, 3, 2)
Year = Mid(DateString, 5, 4)
Hour = Mid(DateString, 9, 2)
Minute = Mid(DateString, 11, 2)
Second = Mid(DateString, 13, 2)

DateVal = DateValue("1/1/1905")
On Error Resume Next
DateVal = DateValue(Month & "/" & Day & "/" & Year) _
  + TimeValue(Hour & ":" & Minute & ":" & Second)
If DateVal = DateValue("1/1/1905") Then
  MsgBox DateString + " is not a valid datetime"
  ParseDate = False
Else
  ParseDate = True
End If
End Function

HTH

 

[ggratecc]

This post saved me alot of time.
Great post.
Greg