Convert 6 digit number into hh:mm:ss format

[kwatts83]

I would like to type a 6 digit number (i.e. 070144) into a cell and have it read as 07:01:44 in that cell. Is this possible? I am not looking to create a formula in a different cell. I just want to type the number and have it show up with the colons in between.

 

[Jonmo1]

Welcome to the board..

It will require VBA code.
This will apply only to column A
Right click the Sheet's Tab, View Code
Paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range
Set MyRange = Intersect(Target, Range("A:A"))
On Error GoTo Handlr
If Not MyRange Is Nothing Then
    Application.EnableEvents = False
    For Each c In MyRange
        c.Value = Format(c.Value, "00\:00\:00")
        c.NumberFormat = "hh:mm:ss"
    Next c
End If
Handlr:
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

I would like to type a 6 digit number (i.e. 070144) into a cell and have it read as 07:01:44 in that cell. Is this possible? I am not looking to create a formula in a different cell. I just want to type the number and have it show up with the colons in between.

If you don't need that number to ever be a real time value (which looks to be the case given your first number is 70), you can simply custom format your cell using this Type format pattern..

00\:00\:00