Hi

One way would be to use some event code on the sheet where the times are
being entered.

Change the Target column number to suit the column number where you are
entering the time data.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 10 Then Exit Sub <--- Change column number to suit
tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen < 3 Or tlen 4 Then
MsgBox ("Invalid Entry")
GoTo endtime
End If

If Target.Value 2359 Then
MsgBox ("Can't enter a time past 23:59")
GoTo endtime
End If

If tlen = 3 Then
result = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
result = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If
Target.Value = Format(result, "hh:mm")
endtime:
Application.EnableEvents = True
End Sub
--
Regards
Roger Govier



"Nevets" wrote in message
...
I am creating a workbook for many users, so I would like to make it as
"idiot
proof" as possible. In several of the cells, I want users to enter time
in a
24 hr format. If I format the cell to accept "hh:mm" times, then the
users
have to enter it exactly that way for it to be accepted. Is there a way
to
set up the cell so that if they enter "hhmm", or "hh.mm", (or even "hmm"
if
the time being entered is before 0959), the program will convert it to
"hh:mm" format when they tab out of the cell?
For example, user wants to enter 08:00 hrs; so if they type 08:00, 08.00,
0800, or 800 in the cell, when they tab out, it will convert to 08:00.
I also want to do something similar with a date field. MS Word does
something like this with text form fields; I can set up a text field such
that if someone enters "sep 7 07" then tabs out of the field, it will be
changed to "Friday September 7, 2007".
Can I get Excel to do what I want?