Automatically convert numbers to time

The most reliable approach is with a small helper formula or a simple VBA event. I’ll show both, starting with the no-VBA solution.

work without VBA (closest workaround)

You can hide the input cell and show only the calculated result.

Example:

• A1 = user types 0925
• B1 = formula:

=TIME(INT(A1/100), MOD(A1,100), 0)

• Format B1 as hh:mm
• Hide column A

To the user, it looks like one cell.

Work with VBA

If you want the same cell to convert immediately after pressing Enter, VBA is required.

VBA Code (Worksheet Change Event)

1. Press ALT + F11
2. Double-click the worksheet containing the time columns
3. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A:F")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub

    Application.EnableEvents = False

    Dim t As Double
    t = Target.Value

    If t >= 0 And t <= 2359 Then
        Target.Value = TimeSerial(Int(t / 100), t Mod 100, 0)
        Target.NumberFormat = "hh:mm"
    End If

    Application.EnableEvents = True

End Sub

What this does

• Works for columns A–F
• Converts 0925 → 09:25
• Converts 1345 → 13:45
• Triggers automatically on Enter

No helper columns
Seamless for users
Handles large files well

Notes

• Users must not type :
• Invalid values (e.g., 2560) are ignored
• Leading zeros are handled automatically

Recommendation

• Shared / locked-down file → Formula method
• Internal / power-user file → VBA method

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.