Time format

A

art

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
S

Stefi

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:
 
A

art

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 said:
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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
S

Stefi

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 said:
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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
A

art

Thanks for the quick response.



Stefi said:
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 said:
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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
A

art

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

Stefi said:
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 said:
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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
S

Stefi

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 said:
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.


:

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
A

art

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

Stefi said:
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 said:
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.


:

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
A

art

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

Pleased let me know.




Stefi said:
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 said:
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.


:

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
A

art

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

Please let me know





Stefi said:
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 said:
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.


:

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 
S

Stefi

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 said:
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.

:


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.


:

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(A2,1)="a",CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0"),"
","AM"),CONCATENATE(TEXT(LEFT(A2,1),"#:\0\0")," ","PM")),"Please enter a or
p").

Thanks,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top