Doug - Thanks so much for your thorough response. I really appreciate it.
I'm in the process of trying it now and will let you know. The reason for
avoiding the colon is that the payroll clerks are quite error prone, and if
they have to enter a colon, that requires two entries, the shift and then the
colon, hence more chance for error. Many of the clerks are good at using the
number pad, but if they have to stop to enter a shift and colon, they take
their hands off the number pad and hence further chance of error. I've
actually seen it happen.
--
CJM


"Doug Glancy" wrote:

CJM,

I've tried to think of all the things they could enter that won't work, but
I've probably missed some.

Paste the following into the worksheet module. It does the conversion for
any value entered into Column B. The long If statement checks for, in
order, 3 or 4 characters, whether it's an integer, if the hour digits are
less than or equal to 24, if the minute digits are less than or equal to 60.
I tried to structure it so that you only have the error message in one
place, but got a compile error on non-numerics until I put that test first.

You have to enter these numbers as military time, e.g. 1430 for 2:30 pm.
One weird result is that 12:30 AM must be entered as "24:30". Al in all I
think I'd just have them enter the colon, but it is an interesting problem.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If IsNumeric(Target) Then
If (Len(Target) = 3 Or Len(Target) = 4) And _
Int(Target) = Target And _
Left$(Target, WorksheetFunction.Max(Len(Target) - 2, 1)) <= 24 And
_
Right$(Target, 2) <= 59 Then
Target = Left$(Target, Len(Target) - 2) & ":" & Right$(Target,
2)
If Left$(Target, 2) = "24" Then
Target = Replace(Target, "24", "0")
End If
Target.NumberFormat = "h:mm AM/PM"
Else
Target.Clear
Target.Select
MsgBox "that's not a time"
End If
Else
Target.Clear
Target.Select
MsgBox "that's not a time"
End If
End If
Application.EnableEvents = True

End Sub

hth,

Doug


"CJM" wrote in message
...
I'm doing something in Excel 2003 which I think is fairly basic. I'm
creating a time entry worksheet function which will be used by
administrators
to enter the daily hours for a group of employees. Instead of entering
"8:30", I would like to avoid typing the colon and have the user into
"830".
I would then like this to be stored and displayed in the cell as "08:30
AM".
This requires a simple calculation to convert the "830" to the proper time
format, but I can't figure out how to return to the active cell a
calculated
value which is calculated based on input from the user. I know there has
to
be a way. Any help would be greatly appreciated.
--
CJM