ExcelBanter - Entering date and time on VBA Form

there are lots of answers to your question because what formats you want to
accept. The DAY and time look alike with out the slashes and colon sign. So
you can't tell the differences beteen 112208 and 012300 (hour, minute,
second) becauwe they are both 6 characters. You can use the following rules

1) 4 characters or less it is a time. We will assume 0123 and 123 are both
1:23
2) 5 or 6 characters is a date 012208 and 12208 will be the same.
3) more than 6 characters will be a date and time

Try this code

Sub test()

DateString = "01152009 123"
'Remove any leading or trailing spaces
DateString = Trim(DateString)

'split into day and time
'assume if there is a space it includes day and time
If InStr(DateString, " ") 0 Then
DayString = Trim(Left(DateString, InStr(DateString, " ") - 1))
TimeString = Trim(Mid(DateString, InStr(DateString, " ") + 1))
Else
'if 4 or less character then string is just time
If Len(DateString) <= 4 Then
TimeString = DateString
DayString = ""
Else
TimeString = ""
DayString = DateString
End If
End If

MyTime = 0
Select Case Len(TimeString)

Case 0 ' no time ok
Case Is <= 4
'Add todays date to the time
MyHour = Val(Left(TimeString, Len(TimeString) - 2))
MyMinute = Val(Right(TimeString, 2))
'include date into time
If DayString = "" Then
MyTime = Int(Now) + TimeSerial(MyHour, MyMinute, 0)
Else
MyTime = TimeSerial(MyHour, MyMinute, 0)
End If
Case Else
MsgBox ("Bad Date/Time")
End Select

Select Case Len(DayString)
Case 0 'no date ok
Case 5, 6 'year will be 2 digits
'month can be 5 or 6 characters
MyMonth = Val(Left(DayString, Len(DayString) - 4))
'Remove month from string
DayString = Mid(DayString, Len(DayString) - 3)
MyDay = Val(Left(DayString, 2))
MyYear = Val(Right(DayString, 2))
MyTime = MyTime + DateValue(MyMonth & "/" & MyDay & "/" & MyYear)
Case 7, 8 'year will be 4 digits
'month can be 5 or 6 characters
MyMonth = Val(Left(DayString, Len(DayString) - 6))
'Remove month from string
DayString = Mid(DayString, Len(DayString) - 5)
MyDay = Val(Left(DayString, 2))
MyYear = Val(Right(DayString, 4))
MyTime = MyTime + DateValue(MyMonth & "/" & MyDay & "/" & MyYear)
Case Else
MsgBox ("Bad Date/Time")
End Select

End Sub

"Tony" wrote:

I have a VBA Form that I want the user to enter a date without having to
enter the slash marks. i.e. User enters 122208, and the text is formatted as
12/22/08 in the textbox. I have seen this on forms in Access. Also, I would
like to do the same with time format. User would enter 0123 to get 1:23. Can
anyone help? Many thanks in advance....

Tony