如何在数字之间插入冒号以使其在Excel中自动成为时间格式？

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

最佳办公生产力工具

在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。 每天将您的工作效率提高50％，并减少数百次鼠标单击！