| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
| Convert time stored as decimalised number to time format | New Users to Excel | |||
| Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
| convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
| Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
Linear Mode
