If you want to enter times other than an exact hour use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
'enter 1245p and get 12:45:00PM
'enter 912a to get 9:12AM
If Target.Column = 4 Then
On Error GoTo endit
Application.EnableEvents = False
If Len(Target) = 4 Then
hr = Left(Target, 1)
mn = Mid(Target, 2, 2)
ap = Right(Target, 1)
Else
hr = Left(Target, 2)
mn = Mid(Target, 3, 2)
ap = Right(Target, 1)
End If
Target.Value = TimeValue(hr & ":" & mn & " " & ap)
NumberFormat = "h:mm AM/PM"
endit:
Application.EnableEvents = True
End If
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Aug 2009 09:06:07 -0700, Demosthenes
wrote:

Hi,

I want to set up a worksheet so that an input of "9a" or "1p" will
autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that?

Thanks,