custom cell format needed

This UDF might do what you want.

Put this in a normal module.

Public Const functionName As String = "cellentry("

Function CellEntry(Optional ByVal inputCell As Range) As String
Rem returns the text last entered in the cell
On Error Resume Next
If inputCell Is Nothing Then Set inputCell = Application.Caller
On Error GoTo 0
If inputCell Is Nothing Then
    CellEntry = vbNullString
Else
    With inputCell.Range("a1").Validation
        On Error Resume Next
        CellEntry = .InputMessage
        If .Parent.Address <> Application.Caller.Address Then Exit Function
        On Error GoTo 0
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertInformation, Formula1:="=(1=1)"
        .ErrorMessage = .Parent.FormulaR1C1
        .InputMessage = CellEntry
        .ShowInput = False
        .ShowError = False
    End With
End If
End Function

and this in the ThisWorkbook code module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim myRange As Range, oneCell As Range, xVal As Variant
On Error Resume Next
Set myRange = Target.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each oneCell In myRange
    With oneCell
        If .HasFormula Then
        Rem formula entered
            If InStr(LCase(.FormulaR1C1), functionName) = 0 Then
                Rem non-ce formula entered, delete ce-Validation
                .Validation.Delete
            Else
                Rem new ce formula entered, update stored formula
                xVal = CellEntry(oneCell)
            End If
        Else
        Rem text entered
            If Application.CutCopyMode Then
                .Validation.Delete
            Else
                If InStr(LCase(.Validation.ErrorMessage), functionName) = 0 Then
                    Rem cell has non-CE validation
                Else
                    Rem set new value for CellEntry and replace formula in cell
                    .Validation.InputMessage = CStr(.Value)
                    Application.EnableEvents = False
                    .FormulaR1C1 = .Validation.ErrorMessage
                    Application.EnableEvents = True
                End If
            End If
        End If
    End With
Next oneCell
End Sub

CellEntry() returns the last text typed into the cell that calls the UDF CellEntry.

To demonstrate:
Enter =CellEntry() in a cell.
Select that cell, it will look empty and =cellentry() will be in the formula bar
Type "cat" and press Return.
The cell will show "cat", but the formula bar will show =cellentry().
Typing into the cell has set the return value of the CellEntry function to "cat".
It is one way to have a cell hold both a formula and a value entered by the user.

In your case, put
=TIMEVALUE(MID("000"&cellentry(),LEN(cellentry()),2)&":"&RIGHT(cellentry(),2))
in any cell.
Entering 1123 will set the value of CellEntry to "1123", and after being evaluated by the formula, the value in the cell will be the serial time 11:23 AM (0.53125).

Entering 930 puts 9:30 AM into the cell, which can be formatted to your taste.
Entering 1458 puts 2:58 PM in the cell.
Entering 035 returns 12:35 AM; 2435 - 12:35 AM tomorow

This can be done with as many cells as you like.

I hope this helps.