How to insert colon between numbers to make them as time format automatically in Excel?
When entering time values in Excel worksheet cells, it can be tedious to manually type colons each time—such as entering "12:34" instead of simply "1234." Users often wish for a convenient way to quickly convert numbers into standard time formats. This guide explores practical methods to automatically insert colons between numbers and format them as time in Excel, saving you effort and reducing data entry errors, whether you're dealing with schedules, timestamps, or logging activity durations.
➤ Auto make the numbers to time format when you entering them with VBA code
➤ Alternative: Text to Columns method
Insert colon between numbers to make them as time format with formulas
If you already have a column of numbers and want to transform them into time format (for example, making "915" into "09:15" or "121530" into "12:15:30") without entering the colons manually, Excel formulas can help you perform this transformation automatically. This method is ideal when you want a straightforward way to process a bulk list of numeric entries already present in your worksheet.
Below you’ll find two formula solutions depending on the number of digits in your numeric entries. Be sure to use the formula that matches your data type for best results.
If the numbers contain3 or4 digits, you can use this formula:
Enter this formula in the adjacent cell (for example, if your number is in cell A1, enter the formula in B1):
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)After entering the formula, press Enter to confirm. Then drag the fill handle down from the corner of the formula cell to automatically apply it to other rows as needed. This approach works best for numbers such as "915" (will be transformed to9:15), or "1234" (becomes12:34). See result preview:
Key point: The formula extracts the hour part from the initial digits and the minute part from the last two digits, setting seconds to zero.
If the numbers have5 or6 digits, try this formula:
Enter this formula in the corresponding cell (e.g., if "123456" is in cell A1, input in B1):
=(INT(A1/10000)&":"&INT(MOD(A1,10000)/100)&":"&MOD(A1,100))+0Confirm with Enter and drag the fill handle down to fill remaining cells. This formula is suitable for numbers like "123456" (becomes12:34:56) or "93005" (transforms to9:30:05). Preview of results is shown below:
Inside the formula, the hour, minute, and second are extracted and concatenated with colons, then added0 to force Excel to recognize it as a time value.
After applying the formulas above, if the output appears as a serial number (e.g., "0.3875") rather than time, format the results as time by selecting the filled range, go to the Home tab, click the Number Format dropdown (often labeled as "General"), and pick Time. This converts the result to an appropriate time display:
Practical tips:
• The cell reference (e.g., A1) must match your actual data location. Adjust the formula references as needed.
• Ensure all your numbers conform to the expected length (3–4 digits or5–6 digits) before applying formulas.
• To convert the formula result to static values, Copy and then Paste Special as Values.
Limitations: These formulas work for numbers structured specifically as hour-minute or hour-minute-second. If data is inconsistent in length, manual adjustment or custom formulas may be needed.
If you get a #VALUE! error, check for blank cells, non-numeric characters, or mismatched numbering. Ensure that only numbers are entered in the source cell.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Auto make the numbers to time format when you entering them with VBA code
For users who frequently enter numeric values and want Excel to instantly format them as time with colons, you can automate the process using VBA code. This solution is especially practical when entering raw data into a fixed range, and it ensures each number is transformed into time format immediately upon entry—reducing repetitive tasks and minimizing typing errors.
Follow these steps to set up the automation:
1. In the worksheet where you want to automatically format numbers as time, locate the sheet tab at the bottom.
2. Right-click the sheet tab and choose View Code from the menu. This opens the Microsoft Visual Basic for Applications editor. Paste the below code into the blank code window that corresponds to your worksheet (not into a standard module):
VBA code: Auto make the numbers to time format:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20160606
Dim xStr As String
Dim xVal As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
With Target
If Not .HasFormula Then
xVal = .Value
Select Case Len(xVal)
Case 1 ' e.g., 1 = 00:01 AM
xStr = "00:0" & xVal
Case 2 ' e.g., 12 = 00:12 AM
xStr = "00:" & xVal
Case 3 ' e.g., 735 = 7:35 AM
xStr = Left(xVal, 1) & ":" & Right(xVal, 2)
Case 4 ' e.g., 1234 = 12:34
xStr = Left(xVal, 2) & ":" & Right(xVal, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
xStr = Left(xVal, 1) & ":" & Mid(xVal, 2, 2) & ":" & Right(xVal, 2)
Case 6 ' e.g., 123456 = 12:34:56
xStr = Left(xVal, 2) & ":" & Mid(xVal, 3, 2) & ":" & Right(xVal, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(xStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
Note: The cell range A1:A20 is used in the example, but you may change this reference to fit your needs (for example, B2:B100, etc.). Only numbers entered in the specified range will be auto-formatted. If you use formulas, those values will not be altered by the macro.
3. Now, when you type a number representing a time (such as102319) within the target cell range, and press Enter, the code interprets and converts the entry into time format automatically (in this example, "10:23:19 AM"). The transformation is instant and requires no further manual formatting:
Helpful reminders:
• If an invalid number is entered, you will see a pop-up message: "You did not enter a valid time."
• Make sure that Application.EnableEvents is not turned off elsewhere in your workbook after running this macro, otherwise automatic conversion will not trigger.
• To reuse this macro in another sheet, repeat the setup steps for that worksheet's code window.
• Limitations: The code is intended for number sequences only (no spaces, colons, or other delimiters). Mixed or non-numeric entries will not be converted.
Best use case: When user input in the worksheet is controlled and the data always matches expected formats and lengths.
Troubleshooting: If the macro does not trigger, check that events are enabled, the correct range is used, and the macro is pasted in the worksheet code window. If your numbers have a different digit structure, you may need to further customize the VBA logic.
Alternative: Insert colon between numbers using Text to Columns to convert to time format
Another practical solution for splitting numbers into time components is Excel's Text to Columns feature, especially useful when you want to separate numbers into hour, minute, and second columns before recombining them as time.
First, select the column with your numbers. Navigate to Data > Text to Columns. Choose Fixed width and set break lines to divide hours, minutes, and seconds based on digit positions. After splitting, use TIME or TIMEVALUE formula to recombine as a time in a new column.
Example: If you split "123456" into three columns "12" (hours), "34" (minutes), "56" (seconds), you can enter:
=TIME(A1,B1,C1)Advantages: No need for complex formulas or VBA, ideal for batch conversion.
Drawbacks: Requires manual adjustment for split points and recombination, not automatic upon entry.
For step-by-step help with Text to Columns, seeofficial Microsoft documentation.
Summary suggestion: For one-off or structured conversions, use the formula or Text to Columns method. For ongoing, real-time entry conversion, apply the VBA automation. Always confirm the actual digit structure of your data before choosing the approach, and clearly define your target cell range to avoid unexpected results. When necessary, back up your data before performing bulk transformations.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in