Forum Discussion

Willybald's avatar
Willybald
Copper Contributor
Jan 04, 2026

Automatically convert numbers to time

Hello 

I have a total of 6 columns in a large file, in which different times have to be entered. 

Is there a way to convert entered numbers to the time format? Background is-I want to bypass the input of the :

Example: I enter 0925 in the field and when I confirm, the value is converted to the time 09:25.

Thank you in advance

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 06, 2026

    This is just a slight variation on NikolinoDE​ 's formula approach.

    The first step is to allow the 4-digit format you require for data input but use number formatting of "00\:00" to display the number as if it were a time in hours and minutes.  You could also use validation to request the input of an integer between 0000 and 2400.

    The appearance of the number may be OK but as a datetime it is not relevant being a date sometime prior to 27 July 1906.  To convert to a time you divide by 100, treat the decimal part as 60ths to get a result in hours and then further divide by 24 to get the time as a proportion of a day.  This could be a worksheet formula, in which case you commit a further range to display the times, or you could simply use a defined name

    inputTime
= DOLLARDE(input/100, 60)/24

    If you use 'inputTime' to reference the 4 digit times it will return a meaningful array of times.  If you have 365 and feel more adventurous you could define a Lambda function to convert the 4-digit integer representation to Excel times

    Worksheet formula
= CONVERT.TIMEλ(input)

where

CONVERT.TIMEλ
= LAMBDA(integerTime, DOLLARDE(integerTime/100, 60)/24)

    If you never perform calculation using the non-standard representation of time, everything after the first two paragraphs can be ignored.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 05, 2026

    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.

Resources