Excel Hrs, Mins and Seconds

If you want to enter, like, "715" and have the worksheet show "07:15" you will need VBA to over-write what you typed. That will require your workbook to be macro-enabled. And it will require you to make a major effort to understand what the code is doing in your workbook. That is necessary not so much for the purpose of enjoying the convenience but to understand what it is not doing as well as what else it might be doing.

The code below is supposed to do exactly what I imagined that you imagined. Enter "2" and the sheet will display "00:02". In short, it adds leading zeroes. It will convert your entry to a real date/time using the current date. If you are going to do calculations bridging midnight this will lead to problems in the future. (Your worksheet doesn't appear to consider this possibility as yet.) If you enter a number that can't be converted to a time the macro will complain. If you enter "1L15" the macro will not complain but process only "115".

Below is the code that does all the work. It needs to be installed in a standard code module. Standard code modules must be inserted by the user. None of the existing modules in your workbook will do. If you haven't done it before google for instructions. The default name will be Module1. I recommend that you rename it, perhaps TeachExcel or Variatus or TimeEntryControl (no spaces allowed). You can rename it in the Properties pane which you may have to enable first. It's in the View menu. Copy the code from here and paste to the code pane of the module you created.

Option Explicit
Enum Nws                        ' worksheet navigation (adjust values to suit)
    ' 065
    NwsFirstDataRow = 4
    NwsNumRows = 31             ' number of rows counting from NwsFirstDataRow
End Enum

Sub WorksheetChange(Target As Range, _
                    Triggers As String, _
                    Optional ByVal FirstDataRow As Long = NwsFirstDataRow, _
                    Optional ByVal NumRows As Long = NwsNumRows)
    ' 065
    
    Dim Trigger         As Range                ' the trigger range as defined above
    Dim Sp()            As String               ' array created from 'Triggers'
    Dim Tmp             As Variant              ' helper
    Dim Tim             As String               ' preparing the time output
    Dim f               As Integer              ' loop counter
    
    ' don't respond to changes of more than 1 cell
    If Target.CountLarge > 1 Then Exit Sub
    
    Sp = Split(Triggers, ",")
    Set Trigger = SetRange(Sp(0), FirstDataRow, NumRows)
    For f = 1 To UBound(Sp)
        Set Trigger = Application.Union(Trigger, SetRange(Sp(f), FirstDataRow, NumRows))
    Next f
    
    If Not Application.Intersect(Target, Trigger) Is Nothing Then
        With Target
            Tmp = .Value
            If Len(Tmp) And (InStr(Tmp, ":") = 0) Then      ' skip blanks and entries formatted as Time
                For f = 1 To Len(Tmp)
                    ' remove non-numeric characters
                    If IsNumeric(Mid(Tmp, f, 1)) Then Tim = Tim & Mid(Tmp, f, 1)
                Next f
                Tim = Right("0000" & Tim, 4)
                Application.EnableEvents = False            ' prevent the change calling this procedure
                On Error Resume Next
                .Value = TimeValue(Left(Tim, 2) & ":" & Right(Tim, 2))
                If Err Then
                    MsgBox "I can't convert the number you entered" & vbCr & _
                           "to a valid time. Please review it.", _
                           vbExclamation, "Invalid time entry"
                    .Select
                    Err.Clear
                End If
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub
Private Function SetRange(ByVal Clms As String, _
                          ByVal FirstDataRow As Long, _
                          ByVal NumRows As Long) As Range
    ' 065
    Dim Tmp             As String               ' helper
    
    Tmp = Trim(Clms)
    If InStr(Tmp, ":") = 0 Then Tmp = Tmp & ":" & Tmp
    With Range(Tmp)
        Set SetRange = Range(Cells(FirstDataRow, .Column), _
                             Cells(FirstDataRow + NumRows - 1, .Column + .Columns.Count - 1))
    End With
End Function

At the top of the code there are two enumerations.
   NwsFirstDataRow = 4
   NwsNumRows = 31
These are default values which you can change here or over-ride. I'll show you how later. The principle you need to understand is that you don't want the macro to convert any number you type anywhere to a time. There are cells where you want this action and many other cells where you don't. The enumeration (Enum) determines the vertical limits: in this setting 31 rows starting from row 4. You can change the numbers but not the names.

Now that you have the code in your workbook you need a mechanism to call it. Until it's called it will do absolutley nothing. The calling is done by the procedure below.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 065
    
    ' define the columns where entries are of Time format by default
    Const Triggers      As String = "E, G:H, J:K, P:Q, S, W:Y"
    
    WorksheetChange Target, Triggers
End Sub

This code must be in the code sheet belonging to the worksheet on which you want the action. In the VB Editor's Project Explorer pane you will see it listed like "Sheet2 (Joseph)". Double-click to open the code pane and paste the code there. Repeat for all the sheets on which you want similar action.

Observe that the Constant Triggers defines the horizontal scope of the action. It's in columns E, G:H, J:K and more. You can modify this string. Just pay attention to have it all surrounded by quotation marks and each column range separated from the others by a comma. You can add, reduce or combine.

Not all your sheets have the same layout. I noticed that the Petrus tab needs a time in column C, followed by E:F. Accordingly, while the code behind all sheets is the same, the Const Triggers can be different on each. In the same way you can also set different FirstDataRow or NamRow for each sheet or for some. Below is another copy of the above code with added explanations how to modify the action calls. I suggest you keep one copy of this in your project for future reference.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 065
    
    ' define the columns where entries are of Time format by default
    Const Triggers      As String = "E, G:H, J:K, P:Q, S, W:Y"
    
    WorksheetChange Target, Triggers
    ' Optionally, you can supply a FirstDataRow and/or NumRows to over-ride
    ' the defaults, applicable only to this sheet.
    ' to specify both values, call the function like this:-
    ' WorksheetChange Target, Triggers, 6, 30
    ' omit the value for 'NumRows' to use the default
    
    ' to specify different 'NumRows' only use this line of code:-
    ' WorksheetChange Target, Triggers, NumRows:=30
End Sub

The calling procedure is called an "event procedure". It reacts to the Change event. As you make a change on its worksheet this procedure runs. It calls the working Sub WorksheetChange in the standard code module, passing information about the cell that was changed as well as the list of Trigger columns. If no values for FirstDataRow or NumRows are provided Sub WorksheetChange will use the defaults. With this information Sub WorksheetChange will first determine whether to take any action at all. It will not take action if you pasted a range of times, just if a single cell was modified. It will also take no action if the changed cell is blank.

If all conditions are met it will change the entry you made to the entry you want.