time format

[Guest]

How do I format a column so that when I put in 6 digit numbers they will
automatically format as hh:mm:ss? When using the Format Cells option and then
Time hh:mm:ss, the digits appear as 00:00:00 when you tab out of the cell.

 

[Harald Staff]

You can't.
1 is a day in Excel. 12 hours is half a day, 0.5.
You want to enter 120000 (which to Excel is 120 000 days, more than 300
years) and suddenly it is 0.5.
Formatting does not change content that way. You need a macro solution.
There's one at Chip's page
http://www.cpearson.com/excel/DateTimeEntry.htm

HTH. Best wishes Harald

 

[Jason Morin]

Assuming column A, format the column as text, then use
this code:

Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Column <> 1 Then Exit Sub 'col A=1,B=2,etc.
If .Count > 1 Then Exit Sub
Application.EnableEvents = False

If IsNumeric(.Value) Then
If Len(.Value) <> 6 Then
MsgBox "Please enter 6 numbers."
GoTo ExitThisSub
ElseIf Left(.Value, 2) > 24 Or _
Mid(.Value, 3, 2) > 59 Or _
Right(.Value, 2) > 59 Then
MsgBox "The maximum time allowed " & _
"is 23:59:59."
GoTo ExitThisSub
End If
Else
MsgBox "Invalid entry."
GoTo ExitThisSub
End If

.NumberFormat = "General"
.Value = TimeSerial(Left(.Value, 2), _
Mid(.Value, 3, 2), Right(.Value, 2))
.NumberFormat = "[hh]:mm:ss"
Application.EnableEvents = True
Exit Sub

ExitThisSub:
.ClearContents
.Select
Application.EnableEvents = True
Exit Sub

End With

End Sub

---
To use the code, right-click on the worksheet tab, go to
View Code, and paste in the code above. Press ALT+Q to
close.

HTH
Jason
Atlanta, GA