How to insert colon between numbers to make them as time format automatically in Excel?
When you insert time format in worksheet cells, have you ever be annoyed with typing the colons each time? In this article, I will talk about how to insert colon when you entering numbers to make them as time format automatically in Excel.
Supposing, you have a list of numbers, to insert the colons and make them as time format, the following formulas can do you a favor.
If the numbers are always 3 or 4 digits you can use this formula:
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0), then drag the fill handle down to the cells that you want to contain this formula, and you will get the following result as you need:
If the numbers are 5 or 6 digits, please apply this formula:
=(INT(A1/10000)&":"&INT(MOD(A1,10000)/100)&":"&MOD(A1,100))+0, and drag the fill handle down to the cells that you want to contain this formula, and you will get number sequence as following screenshot shown:
Then you should format the numbers as time format by clicking Time from the General drop down list under the Home tab, see screenshot:
The following VBA code also can help you to auto convert the numbers to time format when you entering them, please do as this:
1. Go the worksheet that you want to insert colons into numbers automatically.
2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:
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: In the above code, A1:A20 is the cells that you want to insert time later, and you can change the cell reference to your need.
3. And now, when you enter the numbers such as 102319 in cell range A1:A20, and then press Enter key, the number will be formatted as 10:23:19 AM time format automatically as you need.