如何根据Excel单元格日期自动更新状态列？含公式修正与VBA方案

需求说明 #

当在「Scheduled Completion（D列）」或「Completed（E列）」输入日期时，自动将「Status（B列）」更新为对应状态：

• 若Completed日期晚于计划日期 → Late Complete
• 若Completed日期不晚于计划日期 → Complete
• 若无Completed日期，计划日期早于今日 → Late
• 若无Completed日期，计划日期在今日到30天内 → Imminent
• 若无Completed日期，计划日期超过30天 → Scheduled

方案一：修正后的公式方案 #

你之前的公式存在语法错误（多个IF未嵌套、括号不匹配、多余引号），以下是正确的嵌套IF公式，直接在B2单元格输入后下拉填充即可：

=IF(NOT(ISBLANK(E2)),IF(E2>D2,"Late Complete","Complete"),IF(D2<TODAY(),"Late",IF(D2<=TODAY()+30,"Imminent","Scheduled")))

公式逻辑 #

1. 优先检查Completed列（E列）是否有值
2. 有值则对比完成日期与计划日期，输出对应完成状态
3. 无值则根据计划日期与今日的间隔，输出Late/Imminent/Scheduled

方案二：VBA自动触发方案（更适合自动更新） #

如果希望无需手动下拉公式，输入日期后自动更新Status，VBA是更优选择，步骤如下：

1. 打开目标Excel文件，按下Alt+F11打开VBA编辑器
2. 在左侧「项目资源管理器」中找到你的工作表（如Sheet1），双击进入代码窗口
3. 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅监控D列（计划完成）和E列（实际完成）的修改
    If Not Intersect(Target, Me.Range("D:E")) Is Nothing Then
        Dim cell As Range
        ' 处理批量修改（如粘贴多个日期）
        For Each cell In Target
            Dim rowNum As Integer
            rowNum = cell.Row
            ' 跳过表头行（假设表头在第1行，若不是则修改数字）
            If rowNum > 1 Then
                Dim scheduledDate As Variant
                Dim completedDate As Variant
                Dim statusVal As String
                
                ' 获取当前行的计划和完成日期
                scheduledDate = Me.Cells(rowNum, "D").Value
                completedDate = Me.Cells(rowNum, "E").Value
                
                ' 状态判断逻辑
                If Not IsEmpty(completedDate) Then
                    ' 有实际完成日期的情况
                    statusVal = IIf(completedDate > scheduledDate, "Late Complete", "Complete")
                Else
                    ' 无完成日期，按计划日期判断
                    If scheduledDate < Date Then
                        statusVal = "Late"
                    ElseIf scheduledDate <= Date + 30 Then
                        statusVal = "Imminent"
                    Else
                        statusVal = "Scheduled"
                    End If
                End If
                
                ' 更新Status列（B列）
                Me.Cells(rowNum, "B").Value = statusVal
            End If
        Next cell
    End If
End Sub

4. 保存文件为「Excel启用宏的工作簿（.xlsm）」格式，否则宏无法保存

VBA代码说明 #

• Worksheet_Change事件：当工作表中D/E列的单元格被修改时自动触发
• 支持批量操作：比如一次性粘贴多个日期，所有对应行的Status都会自动更新
• 兼容空值处理：如果计划日期为空，Status会保持空（可根据需求调整）
• 若表头不在第1行，修改rowNum > 1中的数字即可

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