Time format
 

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,

Time format
 

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,

Time format
 

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,

Time format
 

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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

Thanks for the quick response.



"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

Here you are, but I can hardly believe that you have only time cells in your
sheet, I think you'll need some reszrictions:
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


Stefi

€žart€� ezt Ã*rta:

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

OK, so what about only in columns D thru G.

"Stefi" wrote:

Here you are, but I can hardly believe that you have only time cells in your
sheet, I think you'll need some reszrictions:
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


Stefi

€žart€� ezt Ã*rta:

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

Is it possible to to that for only columns D thru G?

Pleased let me know.




"Stefi" wrote:

Here you are, but I can hardly believe that you have only time cells in your
sheet, I think you'll need some reszrictions:
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


Stefi

€žart€� ezt Ã*rta:

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

Also, I need to enter sometimes 9:05, can you make that work as well.

Please let me know





"Stefi" wrote:

Here you are, but I can hardly believe that you have only time cells in your
sheet, I think you'll need some reszrictions:
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


Stefi

€žart€� ezt Ã*rta:

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,

Time format
 

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 4 And Target.Column <= 7 Then 'columns D:G
If Right(Target, 1) = "a" Or Right(Target, 1) = "p" Then
timestr = Left(Target, Len(Target) - 1)
timearr = Split(timestr, ":")
hournum = Val(timearr(0)) + (Right(Target, 1) = "p") * -12
minnum = 0
If UBound(timearr) 0 Then minnum = Val(timearr(1))
Target.Value = TimeSerial(hournum, minnum, 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


Regards,
Stefi

€žart€� ezt Ã*rta:

Also, I need to enter sometimes 9:05, can you make that work as well.

Please let me know





"Stefi" wrote:

Here you are, but I can hardly believe that you have only time cells in your
sheet, I think you'll need some reszrictions:
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


Stefi

€žart€� ezt Ã*rta:

One more thing can you please edit it, so it should work for the whole sheet,
not only for column A.

"Stefi" wrote:


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

Regards,
Stefi

€žart€� ezt Ã*rta:

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,