VBA Express : Excel - Enter Times Without the Colon

Excel

Enter Times Without the Colon

Ease of Use

Intermediate

Version tested with

2002

Submitted by:

Steiner

Description:

Allows you to enter times by typing just the digits without the separating colon.

Discussion:

If you have to enter a lot of time values into a worksheet, you'd probably like to use the numpad. At least I don't like to have to type the colon between the digits as I would have to leave the numpad for that. This macro saves me that work. However, be warned: this one is designed to work only with times, if you have a date and a time in a cell, it won't work. And if you enter digits that won't make sense as time (2577--> 25:76????) the number will be interpreted as date (20.01.1907). The code supports 4 cases: Just the minutes: Enter 1 or 2 digits, values greater than 59 are converted to 1 hour and x minutes. Enter 3 or 4 digits ranging from 100 to 2359, and these are converted to hours:minutes (minutes above 59 are added to the next hour) Enter 5 or 6 digits ranging from 10000 to 235959, and they are converted to hours:minutes:seconds 240000 to 245959 will be hours:minutes:second, allowing 0 hours (241245=00:12:45)

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Cells.Count > 1 Then Exit Sub 
     
     'Define the range where you want the code to work (our example is "C:G").
     'Change within the " marks
    If Intersect(Target, Range("C:G")) Is Nothing Then Exit Sub 
     
On Error GoTo errHandler: 
     
    With Target 
        If IsNumeric(.Value) Then 
            Application.EnableEvents = False 
            Select Case .Value 
            Case 0 
                .NumberFormat = "hh:mm" 
            Case 1 To 99 
                .Value = TimeSerial(0, .Value, 0) 
                .NumberFormat = "hh:mm" 
            Case 100 To 2399 
                .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0) 
                .NumberFormat = "hh:mm" 
            Case 10000 To 235959 
                .Value = TimeSerial(Int(.Value / 10000), _ 
                Int((.Value Mod 10000) / 100), .Value Mod 100) 
                .NumberFormat = "hh:mm:ss" 
            Case 240000 To 245959 
                .Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100) 
                .NumberFormat = "hh:mm:ss" 
            Case Else 
            End Select 
        End If 
    End With 
errHandler: 
    Application.EnableEvents = True 
End Sub

How to use:

Open the workbook in which you wish to use the code.
Right-click the sheet tab of the sheet on which you wish the code to run, and choose View Code.
Copy the code above and paste it into the worksheet module window at the right of the Visual Basic Editor (VBE).
Redefine the range where the code should work on by adjusting the the line with intersect, giving it the range you wish (in the above code it works for the whole columns C to G).
Close the VBE using the red X button or Alt + Q.
Save the file if desired.

Test the code:

Enter numbers that can be converted to a time into the worksheet you copied the code into and the range you defined, as described in the discussion above.

Sample File:

EnterTime.zip 8.81KB

Approved by mdmackillop

This entry has been viewed 165 times.