Excel VBA实现工时表时间输入自动将点号替换为冒号

新增能力说明 #

在原有时间自动规范能力基础上，新增点号分隔时间的自动适配：输入以点号分隔的时间（如08.45、8.45）时，系统自动将点号替换为冒号，转换为标准时间格式。

全量支持功能清单 #

• 输入4位纯数字（如0845），自动转换为标准时间格式08:45
• 输入未补零的冒号分隔时间（如8:45），自动规范为补零的标准格式08:45
• 输入3位纯数字（如845），自动转换为标准时间格式08:45
• 输入点号分隔时间（如08.45、8.45），自动替换点号为冒号后转换为标准时间格式08:45
• 输入无效时间值（如08:75、25:61）时，弹出提示*"you did not enter a valid time"*，并将单元格值重置为00:00

改造后完整代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TimeStr As String
    Dim InputVal As String
    On Error GoTo EndMacro

    ' 仅处理指定的工时录入区域
    If Application.Intersect(Target, Range("B5:AC126")) Is Nothing Then
        Exit Sub
    End If
    ' 选中多单元格时不触发逻辑
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    ' 空值、已被识别为合法时间（Excel时间为0-1区间的小数）直接跳过
    If Target.Value = "" Or Target.Value < 1 Then
        Exit Sub
    End If

    Application.EnableEvents = False
    With Target
        If .HasFormula = False Then
            ' 第一步：统一将输入内容中的点号替换为冒号，适配点号分隔的输入习惯
            InputVal = Trim(CStr(.Value))
            InputVal = Replace(InputVal, ".", ":")
            
            ' 如果已经包含冒号分隔符，直接走合法性校验，无需按长度拼接
            If InStr(InputVal, ":") > 0 Then
                TimeStr = InputVal
            Else
                ' 无分隔符的纯数字输入，按长度规则拼接为时间格式
                Select Case Len(InputVal)
                    Case 1 ' 例：输入1 = 01:00
                        TimeStr = Left(InputVal, 1) & ":00"
                    Case 2 ' 例：输入12 = 12:00
                        TimeStr = Left(InputVal, 2) & ":00"
                    Case 3 ' 例：输入735 = 7:35
                        TimeStr = Left(InputVal, 1) & ":" & Right(InputVal, 2)
                    Case 4 ' 例：输入1234 = 12:34
                        TimeStr = Left(InputVal, 2) & ":" & Right(InputVal, 2)
                    Case 5 ' 例：输入12345 = 1:23:45
                        TimeStr = Left(InputVal, 1) & ":" & Mid(InputVal, 2, 2) & ":" & Right(InputVal, 2)
                    Case 6 ' 例：输入123456 = 12:34:56
                        TimeStr = Left(InputVal, 2) & ":" & Mid(InputVal, 3, 2) & ":" & Right(InputVal, 2)
                    Case Else
                        Err.Raise 0
                End Select
            End If
            ' 验证时间合法性并写入单元格
            .Value = TimeValue(TimeStr)
        End If
    End With

    Application.EnableEvents = True
    Exit Sub

EndMacro:
    MsgBox "you did not enter a valid time"
    ' 无效输入重置为00:00
    If Not Target Is Nothing Then
        Target.Value = "00:00"
    End If
    Application.EnableEvents = True
End Sub

关键改动说明 #

• 在格式判断逻辑最前端新增点号替换为冒号的处理，不影响原有纯数字、冒号分隔输入的处理逻辑
• 新增分隔符存在性判断，避免替换点号后走长度拼接逻辑出现格式错误
• 修复原代码错误分支中未定义MyDate变量、无效输入未按要求重置为00:00的问题
• 保留原有的区域限制、多选跳过、公式单元格跳过的判断逻辑，不改变原有使用习惯

内容的提问来源于stack exchange，提问作者Scott