Having trouble formatting times

[John Oliver]

I've received a few good pointers, but haven't found the magic
combination yet. I want to be able to enter times without using any
seperators. I have the VBA code that does that, but the formats aren't
coming out right.

Here's what I'm getting:
(number on left is raw input for cell, value on right is result)

1 - 00:01:00
12 - 00:12:00
123 - 01:23:00
1234 - 12:34:00
12345 - 01:23:45
123456 - 12:34:56

Here's what I want to get:

1 - 00:00:01
12 - 00:00:12
123 - 00:01:23
1234 - 00:12:34
12345 - 01:23:45
123456 - 12:34:56

How can I get that result?

 

[Ron de Bruin]

See this site John

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[John Oliver]

See this site John

http://www.cpearson.com/excel/DateTimeEntry.htm

Been there, done that It doesn't answer my question. It gets me to
where I am now, not where I want to be. I know nothing about VBA, and
don't know if changing the VB script is what needs to happen, or if
there's a trick of formatting, or ???

 

[Dave Peterson]

Mostly stolen from a post by Harald Staff:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 10000 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

 

[John Oliver]

Mostly stolen from a post by Harald Staff:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 10000 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

Boolyah! That did it! If there was a trophy, you'd get it...

 

[Dave Peterson]

I'm sure Harald would say you're welcome. <vbg>

Boolyah! That did it! If there was a trophy, you'd get it...

--
John Oliver, CCNA http://www.john-oliver.net/
Linux/UNIX/network consulting http://www.john-oliver.net/resume/
*** sendmail, Apache, ftp, DNS, spam filtering ***
**** Colocation, T1s, web/email/ftp hosting ****