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.
Insert colon between numbers to make them as time format with formulas
Auto make the numbers to time format when you entering them with VBA code
Insert colon between numbers to make them as time format with formulas
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:
Auto make the numbers to time format when you entering them with VBA code
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.
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!
