Displaying video counter time in hh:mm:ss

[Professor22]

How do I display times in a standard format? As shown in the image below, I have a series of timestamps from videos where users enter the information without leading zeroes for the hour. I can't find a formatting option that will display the entries in a consistent h:mm:ss format. I'm sure there must be a very simple answer, but I just can't see it. Any help would be gratefully received.

 

[Anthony47]

Excel interprets XX:YY as XX hours + YY minutes; or XX:YY:ZZ as hours, minutes and ZZ seconds
If you know that your input will always be in XX-minutes : YY-seconds then you might use the WorksheetChange event to adapt YOUR format to the Excel one. For example using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 1 And Target.Value <> "" Then       '<<< 1=Column A
            Application.EnableEvents = False
            On Error Resume Next
                Target.Value = Target.Value / 60
            On Error GoTo 0
            Application.EnableEvents = True
        End If
        Target.NumberFormat = "[hh]:mm:ss"
End If
End Sub

Insert this code into the vba class module of your workbook:
-right click on the tab with the name of the worksheet
-select Display code. This will open the vba editor at the right position
-copy the code and paste it into the vba page
Customize the line marked <<< to point to the column where the timing will be typed in; I assumed this is column 1=A

Then return to the worksheet and in column A type for example 66:20 (to mean 66 minutes and 20 seconds): the macro will convert your input to a canonical 1:06:20
But this requires that the user strictly comply with the rule of typing MM:SS and not for example HH:MM:SS or MM or SS
Try...

 

[Anthony47]

I realized that I inserted the NumberFormat instruction outside the If /End If, but that is wrong.
The correct code is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 1 And Target.Value <> "" Then       '1=Column A
            Application.EnableEvents = False
            On Error Resume Next
                Target.Value = Target.Value / 60
            On Error GoTo 0
            Application.EnableEvents = True
            Target.NumberFormat = "[hh]:mm:ss"    '*****
        End If
End If
End Sub

Sorry

 

[Professor22]

I realized that I inserted the NumberFormat instruction outside the If /End If, but that is wrong.
The correct code is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column = 1 And Target.Value <> "" Then       '1=Column A
            Application.EnableEvents = False
            On Error Resume Next
                Target.Value = Target.Value / 60
            On Error GoTo 0
            Application.EnableEvents = True
            Target.NumberFormat = "[hh]:mm:ss"    '*****
        End If
End If
End Sub

Sorry

That's perfect, thank you so much!

 

[Anthony47]

Thank you for the feedback