PDA

View Full Version : Solved: Enter Times Without the Colon



oceanus
06-16-2012, 11:26 AM
As I was searching Google for help with entering time without using a colon, I found Steiner's VBA code to Enter Times Without the Colon to be pretty helpful. Now I'm wanting to be able to use a similar code to format, for example, "345", as minutes and seconds - mm:ss or 0:03:45 AM. Steiner's code now formats my entry of "345" as hours and minutes or 3:45:00 AM. I think I could divide the results of the code by 60 and that should change it to min and sec but I'm at a loss on how to do that.

Here is Steiner's code I copied from his post Enter Times Without the Colon.

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

Thanks for your help.

Bob Phillips
06-17-2012, 02:07 AM
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 5959
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 1000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
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

mikerickson
06-17-2012, 02:57 AM
Perhaps
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tString As String

With Target
If .Cells.Count = 1 And 3 <= .Column And .Column <= 7 Then
tString = Right(String(6, "0") & CStr(.Value), 6)

If IsNumeric(tString) Then
tString = WorksheetFunction.Replace(WorksheetFunction.Replace(tString, 5, 0, ":"), 3, 0, ":")

If IsDate(tString) Then
On Error GoTo ErrorOut
Application.EnableEvents = False
.Value = CDate(tString)
End If

End If
End If
End With
ErrorOut:
Application.EnableEvents = True
End Sub

oceanus
06-17-2012, 09:59 AM
Thanks for the quick help.

I was able to "fix" my code after I better understood what Int, Mod and TimeSerial meant and how they could be used to "dice" my number into the time format. As far as I can tell, this code can be used up to 99 hr, 59 min, 59 sec until it falters and fails. It's not as simple as xld's or mikerickson's but it's working for me now and I'm over working on it. You've been a big help!

Thanks a bunch.




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("E11")) 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:ss"
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 999 'up to 10 min 39 sec
.Value = TimeSerial(Int(0), Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 1000 To 9999 '10 min 00 sec to 99 min 99 sec (1:40:39)
.Value = TimeSerial(Int(0), Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 10000 To 235959 '1 hr 00 min 00 sec (1:00:00) to 23 hr 59 min 59 sec (23:59:59)
.Value = TimeSerial(Int(.Value / 10000), Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 240000 To 995959 '24 hr 00 min 00 sec (24:00:00) to 99 hr 59 min 59 sec (99:59;59)
.Value = TimeSerial(Int(.Value / 10000), Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "[h]:mm:ss;@"
Case Else
Range("e11").Value = "Beyond Reason!"
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub