Force users to enter a range of cell in dd/mm/yy hh:mm format

[TATA STEEL]

I have created a template which is filled by users for date & time. Since
users are novice, can I force users to enter the cell values only in
dd/mm/yy hh:mm format?

For ex if a user wants to enter 10th May 09 10:00Am & by mistake he types
10/5/09 10.00 ( uses . instead of colon) excel misunderstands.

 

[JLGWhiz]

The only way that I can think of to be sure the date is the correct format,
is to set up a UserForm with a ListBoxes for Year, Month and Day of Month,
that the user selects from and then have code that transefers the value of
their selection to the worksheet range that has the format you want to use.
This might be more than you wanted to do.

 

[Bernard Liengme]

You could use Date | Validate since 10/5/09 10:00 is a valid 'date' while
10/5/09 10.00 is not
The Validation dialog requires you enter start and end date but you can
enter some dummy values if this is unimportant to you
best wishes

 

[Gary''s Student]

If you input as a string, you can completely validate the format of the input:

Sub GetInput()
Dim s As String
s = Application.InputBox(Prompt:="give me data", Type:=2)
If s Like "##[/]##[/]##[ ]##[:]##" Then
MsgBox ("O.K.")
Else
MsgBox ("N.G.")
End If
End Sub

Note this only checks that the pattern is corrrect.

You can add additional checks, like is the month between 01 and 12
or
is the day between 01 and 31
or
is the hour between 01 and 12
or
is the minute between 00 and 59

If all is good, you can convert the string into a date/time.
If all is not good, prod the user into making another entry.