Hello:

I have the vba code below but I need some changes. I want to make that when
I enter in a cell 5a it should automatically format it to 5:00 AM and when I
enter 7p it should format it to 7:00 PM and so on. However I also need that
when I enter 5:05a it should format to 5:05 AM. Is there a way to do this?

Please let me know.

The code I have is as follows:

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
Target.Value = ""
Target.Select
MsgBox "Enter a or p!"
End If
End If
Application.EnableEvents = True
End Sub