Convert whole number to time

CBass5652 

You do need VBA for this.

Let's say you want to be able to enter times as whole numbers in E1:E10.

Apply the desired time format to these cells.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim TimeStr As String

    On Error GoTo EndMacro
    ' Change the range E1:E10 as needed
    If Application.Intersect(Target, Range("E1:E10")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.HasFormula Then Exit Sub

    Application.EnableEvents = False
    With Target
            Select Case Len(.Value)
                Case 1 ' e.g., 1 = 00:01 AM
                    TimeStr = "00:0" & .Value
                Case 2 ' e.g., 12 = 00:12 AM
                    TimeStr = "00:" & .Value
                Case 3 ' e.g., 735 = 7:35 AM
                    TimeStr = Left(.Value, 1) & ":" & _
                    Right(.Value, 2)
                Case 4 ' e.g., 1234 = 12:34
                    TimeStr = Left(.Value, 2) & ":" & _
                    Right(.Value, 2)
                Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
                    TimeStr = Left(.Value, 1) & ":" & _
                    Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
                Case 6 ' e.g., 123456 = 12:34:56
                    TimeStr = Left(.Value, 2) & ":" & _
                    Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
                Case Else
                    Err.Raise 0
            End Select
            .Value = TimeValue(TimeStr)
    End With
    Application.EnableEvents = True
    Exit Sub

EndMacro:
    MsgBox "You did not enter a valid time"
    Application.EnableEvents = True
End Sub

Instruct the users to allow macros when they open the workbook.

Source: Date and Time Entry