| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time input simplification
Hi,
I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Use the following event macro:
Private Sub Worksheet_Change(ByVal Target As Range) Dim s As String s = Target.Value lett = Right(s, 1) If lett < "a" And lett < "p" Then Exit Sub Application.EnableEvents = False If lett = "a" Then Target.FormulaR1C1 = Left(s, 1) & ":00 AM" Else Target.FormulaR1C1 = Left(s, 1) & ":00 PM" End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200901 "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Another option is to setup some AutoCorrect rules.
ToolsAutoCorrect Options Replace: 9a With: 9:00 AM AddOK -- Biff Microsoft Excel MVP "Demosthenes" wrote in message ... Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Right click the sheet tab and view Code...Paset the below code and try
entering data in Col A and Col B/. Adjust the columns to suit your requirement... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Replace(Target.Text, ":", "") Like "*#a" Or _ Replace(Target.Text, ":", "") Like "*#p" Then Target = Left(Target.Text, Len(Target.Text) - 1) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
If you want to enter times other than an exact hour use this code.
Private Sub Worksheet_Change(ByVal Target As Range) 'enter 1245p and get 12:45:00PM 'enter 912a to get 9:12AM If Target.Column = 4 Then On Error GoTo endit Application.EnableEvents = False If Len(Target) = 4 Then hr = Left(Target, 1) mn = Mid(Target, 2, 2) ap = Right(Target, 1) Else hr = Left(Target, 2) mn = Mid(Target, 3, 2) ap = Right(Target, 1) End If Target.Value = TimeValue(hr & ":" & mn & " " & ap) NumberFormat = "h:mm AM/PM" endit: Application.EnableEvents = True End If End Sub Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 09:06:07 -0700, Demosthenes wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Thanks! That seems to work.
"Jacob Skaria" wrote: Right click the sheet tab and view Code...Paset the below code and try entering data in Col A and Col B/. Adjust the columns to suit your requirement... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Replace(Target.Text, ":", "") Like "*#a" Or _ Replace(Target.Text, ":", "") Like "*#p" Then Target = Left(Target.Text, Len(Target.Text) - 1) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
That seems to work, but only for the times in the macro (12:45 and 9:12). Is
there a way to generalize it? I.e., so you don't have to put every possible time in the macro? Thanks, "Gord Dibben" wrote: If you want to enter times other than an exact hour use this code. Private Sub Worksheet_Change(ByVal Target As Range) 'enter 1245p and get 12:45:00PM 'enter 912a to get 9:12AM If Target.Column = 4 Then On Error GoTo endit Application.EnableEvents = False If Len(Target) = 4 Then hr = Left(Target, 1) mn = Mid(Target, 2, 2) ap = Right(Target, 1) Else hr = Left(Target, 2) mn = Mid(Target, 3, 2) ap = Right(Target, 1) End If Target.Value = TimeValue(hr & ":" & mn & " " & ap) NumberFormat = "h:mm AM/PM" endit: Application.EnableEvents = True End If End Sub Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 09:06:07 -0700, Demosthenes wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Did you try it when entering non-exact times like 930a or 1245p?
I think you may be disappointed in results although Jacob did give you literally what you described in your post. Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 14:22:02 -0700, Demosthenes wrote: Thanks! That seems to work. "Jacob Skaria" wrote: Right click the sheet tab and view Code...Paset the below code and try entering data in Col A and Col B/. Adjust the columns to suit your requirement... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Replace(Target.Text, ":", "") Like "*#a" Or _ Replace(Target.Text, ":", "") Like "*#p" Then Target = Left(Target.Text, Len(Target.Text) - 1) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
The macro contains no code for 12:45 or 9:12 or any other specific time.
Those two times are strictly examples and are preceded by an apostrophe so are not part of the code. Have you tried the code with any other numbers? Simply enter any string like 123a or 534p in column D Gord On Tue, 25 Aug 2009 15:06:01 -0700, Demosthenes wrote: That seems to work, but only for the times in the macro (12:45 and 9:12). Is there a way to generalize it? I.e., so you don't have to put every possible time in the macro? Thanks, "Gord Dibben" wrote: If you want to enter times other than an exact hour use this code. Private Sub Worksheet_Change(ByVal Target As Range) 'enter 1245p and get 12:45:00PM 'enter 912a to get 9:12AM If Target.Column = 4 Then On Error GoTo endit Application.EnableEvents = False If Len(Target) = 4 Then hr = Left(Target, 1) mn = Mid(Target, 2, 2) ap = Right(Target, 1) Else hr = Left(Target, 2) mn = Mid(Target, 3, 2) ap = Right(Target, 1) End If Target.Value = TimeValue(hr & ":" & mn & " " & ap) NumberFormat = "h:mm AM/PM" endit: Application.EnableEvents = True End If End Sub Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 09:06:07 -0700, Demosthenes wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
A revised one..
6a--12:06 AM 26a--12:26 AM 126a--1:26 AM 1206a-- 12:06 AM 1226a-- 12:26 AM Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Target.Text Like "*#a" Or _ Target.Text Like "*#p" Then Target = Left(Format(Left(Target.Text, Len(Target.Text) - 1), "0000"), 2) & _ ":" & Mid(Format(Left(Target.Text, Len(Target.Text) - 1), "0000"), 3, 2) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Gord Dibben" wrote: Did you try it when entering non-exact times like 930a or 1245p? I think you may be disappointed in results although Jacob did give you literally what you described in your post. Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 14:22:02 -0700, Demosthenes wrote: Thanks! That seems to work. "Jacob Skaria" wrote: Right click the sheet tab and view Code...Paset the below code and try entering data in Col A and Col B/. Adjust the columns to suit your requirement... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Replace(Target.Text, ":", "") Like "*#a" Or _ Replace(Target.Text, ":", "") Like "*#p" Then Target = Left(Target.Text, Len(Target.Text) - 1) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time input simplification
Looking good Jacob
Gord On Tue, 25 Aug 2009 21:40:10 -0700, Jacob Skaria wrote: A revised one.. 6a--12:06 AM 26a--12:26 AM 126a--1:26 AM 1206a-- 12:06 AM 1226a-- 12:26 AM Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Target.Text Like "*#a" Or _ Target.Text Like "*#p" Then Target = Left(Format(Left(Target.Text, Len(Target.Text) - 1), "0000"), 2) & _ ":" & Mid(Format(Left(Target.Text, Len(Target.Text) - 1), "0000"), 3, 2) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Gord Dibben" wrote: Did you try it when entering non-exact times like 930a or 1245p? I think you may be disappointed in results although Jacob did give you literally what you described in your post. Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 14:22:02 -0700, Demosthenes wrote: Thanks! That seems to work. "Jacob Skaria" wrote: Right click the sheet tab and view Code...Paset the below code and try entering data in Col A and Col B/. Adjust the columns to suit your requirement... Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:B")) Is Nothing Then If Replace(Target.Text, ":", "") Like "*#a" Or _ Replace(Target.Text, ":", "") Like "*#p" Then Target = Left(Target.Text, Len(Target.Text) - 1) & _ IIf(Target.Text Like "*#a", " AM", " PM") End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Demosthenes" wrote: Hi, I want to set up a worksheet so that an input of "9a" or "1p" will autocorrect to "9:00 AM" and "1:00 PM." What's the best way to do that? Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Simplification Please ... | Excel Discussion (Misc queries) | |||
| Simplification of Matrix | Excel Discussion (Misc queries) | |||
| Simplification help | Excel Worksheet Functions | |||
| can you input time (hh:mm:ss) without having to input the colon i. | Excel Discussion (Misc queries) | |||
| formula simplification | Excel Worksheet Functions | |||
Linear Mode
