Time entry

srburk · Mar 26th, 2002, 08:10 AM

I am currently coordinating a time study to measure productivity. Employees must enter different times into a simple spreadsheet each day and then email their spreadsheet to me.

The complaint has been that they don't like entering the ":" in the time.

How do I format the cell to act like an input mask... all they would have to do is enter the numbers. The field would automatically format.

Example:

They enter: 1104
The cell shows: 11:04 AM

Thanks for any help you can offer!

Dave Hawley · Mar 26th, 2002, 08:36 AM

Hi Scott

Right click on the sheet name tab. select "view Code" and paste in this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End If
End If
End Sub

This restricts the automatic time conversion to A1:A1000

Mar 26th, 2002, 08:10 AM	#1
srburk Board Regular Join Date: Mar 2002 Posts: 147	I am currently coordinating a time study to measure productivity. Employees must enter different times into a simple spreadsheet each day and then email their spreadsheet to me. The complaint has been that they don't like entering the ":" in the time. How do I format the cell to act like an input mask... all they would have to do is enter the numbers. The field would automatically format. Example: They enter: 1104 The cell shows: 11:04 AM Thanks for any help you can offer! __________________ Scott Burk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 08:36 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Scott Right click on the sheet name tab. select "view Code" and paste in this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End If End If End Sub This restricts the automatic time conversion to A1:A1000