基于工时在VB中实现自动生成固定完工日期的需求

我是Excel新手，正在制作首张工时卡。目前已在JobHours工作表按Job ID统计工时，设置了当工时≥77小时时自动勾选完成复选框，但现在需要在相邻的“完工日期”单元格自动生成固定日期戳（生成后不再随工时变动或时间推移更新）。

试过NOW()、TODAY()函数，但日期会随数据变化或时间更新；现有VB代码仅支持手动勾选复选框时生成日期，无法适配工时自动触发复选框的场景，需要修改代码或实现新功能来满足需求。

现有工作表结构 #

• Sheet1（TimeCard）：
  • A列：Job ID
  • B列：Punch in Date
  • C列：Time In
  • D列：Lunch In
  • E列：Lunch Out
  • F列：Time Out
  • G列：Total Hours（公式：=((F2-C2)-(E2-D2))*24）
  • H列：Job ID(0-12)
  • I列：Jobs(Addresses)
• Sheet2（JobHours）：
  • A列：Job ID
  • B列：Job(Address)
  • C列：Hours Worked（公式：=SUMIF(TimeCard!A:A,"1",TimeCard!G:G)）
  • D列：复选框（由E列公式自动控制）
  • E列：TRUE/FALSE（公式：=IF(C2>=77,TRUE,FALSE)）
  • F列：Start Date
  • G列：Complete Date（需要生成固定日期戳）
  • H列：Average Hours

现有VB代码 #

Attribute VB_Name = "Module1"
Sub CheckBox_TimeStamp()
Dim VarCheckBox As CheckBox
Set VarCheckBox = ActiveSheet.CheckBoxes(Application.Caller)
With VarCheckBox.TopLeftCell.Offset(1, 3)
 If VarCheckBox.Value = xlOff Then
     .Value = ""
 Else
     .Value = Date & " " & Time
     .EntireColumn.AutoFit
 End If
End With
End Sub

解决方案：使用工作表计算事件自动生成固定日期戳 #

因为复选框是由E列的公式自动触发的，直接监控工作表的计算事件，就能在工时达标时自动写入固定日期戳，且仅写入一次。

操作步骤 #

1. 右键JobHours工作表标签 → 选择「查看代码」打开代码窗口
2. 粘贴以下代码：

Private Sub Worksheet_Calculate()
    Dim rng As Range
    Dim cell As Range
    
    ' 监控E列（TRUE/FALSE判断列）的所有数据行
    Set rng = Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row)
    
    Application.EnableEvents = False ' 防止循环触发计算
    For Each cell In rng
        ' 当工时达标（E列变为TRUE）且完工日期为空时，写入当前日期时间
        If cell.Value = True And cell.Offset(0, 2).Value = "" Then
            cell.Offset(0, 2).Value = Now()
            cell.Offset(0, 2).NumberFormat = "yyyy-mm-dd hh:mm:ss" ' 设置日期显示格式
        End If
        ' 可选：如果工时退回77小时以下，清空完工日期
        ' If cell.Value = False Then
        '     cell.Offset(0, 2).Value = ""
        ' End If
    Next cell
    Application.EnableEvents = True
End Sub

代码说明 #

• 每次工作表重新计算（比如TimeCard表更新工时导致JobHours的C列、E列变化）时触发
• 仅在工时首次达标（E列从FALSE变为TRUE）且完工日期为空时写入日期，写入后不会随后续计算更新
• 关闭EnableEvents避免代码触发循环计算

如果需要保留手动勾选复选框生成日期的功能，可以继续保留原有的CheckBox_TimeStamp子程序，两种触发方式会同时生效。

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