Time input simplification

  • Thread starter Thread starter Demosthenes
  • Start date Start date
D

Demosthenes

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,
 
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
 
Another option is to setup some AutoCorrect rules.

Tools>AutoCorrect Options
Replace: 9a
With: 9:00 AM
Add>OK
 
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
 
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
 
Thanks! That seems to work.

Jacob Skaria said:
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
Click to expand...
 
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,
 
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
 
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
 
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
 
Looking good Jacob

Gord

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
Click to expand...
 
Back
Top