I need to be able to type numbers into a cell and then have the cell return a
time. I have a sheet where I want the agent to be able to type in say 123p
and get 1:23pm in return.

On Mar 12, 6:38 pm, cpolley wrote:
I need to be able to type numbers into a cell and then have the cell return a
time. I have a sheet where I want the agent to be able to type in say 123p
and get 1:23pm in return.

The following event procedure will do what you want. Notice that the
IF in the second line tests, e.g. if the cell in question is in column
D (Target.column = 4), thus I don't know if it corresponds to the
range of cells which you want to demonstrate this behavior. This is a
worksheet event procedure, thus you will insert it in the code module
of the sheet where you want this to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
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"
Application.EnableEvents = True
End If
End Sub

To install:
Go to the sheet. Right-click the sheet tab. Choose View Code... This
will take you to the VBA IDE. Paste the above code to the module
window.

Write back if you need instructions on how to limit the cell range for
which this will apply.

HTH
Kostis Vezerides

Thank you so much! I would need it to apply to only cells A12 thru A32 if
possible. You've been a tremendous help, thank you!

"cpolley" wrote:

I need to be able to type numbers into a cell and then have the cell return a
time. I have a sheet where I want the agent to be able to type in say 123p
and get 1:23pm in return.

If so, then this is the modified code for these two cells:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 12 And Target.Row <= 32 Then
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"
Application.EnableEvents = True
End If
End Sub

Regards,
Kostis

On Mar 12, 7:55 pm, cpolley wrote:
Thank you so much! I would need it to apply to only cells A12 thru A32 if
possible. You've been a tremendous help, thank you!

"cpolley" wrote:
I need to be able to type numbers into a cell and then have the cell return a
time. I have a sheet where I want the agent to be able to type in say 123p
and get 1:23pm in return.