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,

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,

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,

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,

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,

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,

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,

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,

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,

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,

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,