Excel VBA: Automatically Convert 4-Digit Numbers to HH:MM Time
Question details
The user needs Excel to automatically interpret three- or four-digit numeric entries as real times.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet contains entries such as 1120 or 0650, and the user wants them converted automatically to 11:20 and 06:50.
- Observed behavior
- Excel number formatting alone does not convert a four-digit number into a time value; a worksheet event macro is required.
Confirm that macros are allowed in the workbook and that the target cells are not protected. Save the file as an Excel Macro-Enabled Workbook (*.xlsm) before adding the event code.
Use a Worksheet_Change event to convert entries into real Excel times
Use this solution when users type three- or four-digit numbers directly into worksheet cells and need automatic HH:MM conversion.
The event macro checks the entered value, pads three-digit entries such as 650 to 0650, validates the hour and minute ranges, and stores the result as a real Excel time. It also prevents the event from repeatedly triggering itself.
In Excel, right-click the target sheet tab and choose View Code. This opens the Visual Basic Editor in the correct worksheet module.
Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim valueEntered As Variant Dim digits As String Dim hours As Long Dim minutes As Long If Target.Cells.CountLarge > 1 Then Exit Sub If Target.HasFormula Then Exit Sub valueEntered = Target.Value2 If Not IsNumeric(valueEntered) Then Exit Sub If Len(CStr(CLng(valueEntered))) < 3 Or Len(CStr(CLng(valueEntered))) > 4 Then Exit Sub If CLng(valueEntered) < 0 Or CLng(valueEntered) > 2359 Then Exit Sub digits = Format(CLng(valueEntered), "0000") hours = CLng(Left$(digits, 2)) minutes = CLng(Right$(digits, 2)) If hours > 23 Or minutes > 59 Then Exit Sub On Error GoTo SafeExit Application.EnableEvents = False Target.Value = TimeSerial(hours, minutes, 0) Target.NumberFormat = "hh:mm" SafeExit: Application.EnableEvents = True End Sub
Return to Excel, select File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file. If prompted, choose Enable Content when reopening it.
Type 1120 in a worksheet cell and press Enter; it should display 11:20. Type 0650 and press Enter; Excel stores the numeric entry as 650 and the macro formats it as 06:50.
Convert selected values with a standard VBA macro
Use this approach when the numbers already exist in a range and should be converted in one batch instead of during data entry.
Try WPS Office for Compatible Spreadsheet Time Formatting
WPS Office is a free, lightweight Microsoft Office alternative with a familiar spreadsheet interface and support for common Excel formats. Open and edit XLSX or XLSM workbooks, then migrate your existing worksheets with minimal layout changes.
Frequently Asked Questions
Why does changing the number format not turn 1120 into 11:20 in Excel?
Number formatting changes how a stored value is displayed, but 1120 is still the number 1120 rather than an Excel time serial. A formula or VBA procedure must split the digits into hours and minutes and create a time with TimeSerial.
Where should the Worksheet_Change code be pasted?
Right-click the relevant worksheet tab, choose View Code, and paste the event procedure into that worksheet's code module. Do not paste it into a standard Module or ThisWorkbook if it must respond only to edits on that sheet.
Can the macro preserve an entry such as 0650?
Yes. Because Excel may store 0650 as the number 650, the macro pads the value with Format(CLng(valueEntered), "0000") before extracting the hour and minute portions.
What entries will the macro reject?
The sample code accepts three- or four-digit numeric entries and rejects values outside the 0000-2359 range, hours above 23, or minutes above 59. Values such as 1265 and 2500 are therefore left unchanged.
Why must the workbook be saved as XLSM?
Excel removes VBA code from a standard XLSX workbook. Choose Excel Macro-Enabled Workbook (*.xlsm) in File > Save As so the Worksheet_Change event remains available.
