Thanks, but can you also make that if I only enter a number it should not
only pop up a window saying to wnter a "a" or "p", butg it should go back to
that cell and not let you enter only a number. (As of now, it lets you enter
a number without a "a" or "p", only i a pop up window comes up).

Thanks.


"Stefi" wrote:

Try this event sub (it works in column A):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then 'column A
If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then
hournum = Val(Left(Target, Len(Target) - 1))
If Right(Target, 1) = "p" Then hournum = hournum + 12
Target.Value = TimeSerial(hournum, 0, 0)
Target.NumberFormat = "[$-409]h:mm AM/PM;@"
Else
MsgBox "Enter a or p!"
End If
End If
Application.EnableEvents = True
End Sub

Post if you need help to install it!
Regards,
Stefi


€žart€� ezt Ã*rta:

Hello all:

I was wondering if I can make that when I enter in a cell 5p it should
automatically format as 5:00 PM and so on. And if I enter 5 without a p or a
then it should note that you must enter a "p" or "a". I know how to do it in
a seperate cell as below. But was wondering if I can enter the formula in the
same cell where I put the number, in the format cell feature or somewhere
else. Any help would be appriciated.

The formula I use is:
=IF(OR(RIGHT(A2,1)="a",RIGHT(A2,1)="p"),IF(RIGHT(A 2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"), " ","PM")),"Please enter a or
p").

Thanks,