Excel状态变更日期锁定方案咨询：如何保留历史记录不被覆盖？

方案一：使用IF函数结合迭代计算（无需隐藏列，需开启迭代） #

要保留已生成的日期、避免状态变更时清空，需让公式优先保留已有值，仅在首次触发对应状态时生成日期。此方案需开启迭代计算并限制迭代次数为1，不会影响其他公式：

1. 开启迭代计算：

   • Excel：文件 > 选项 > 公式 > 勾选“启用迭代计算”，设置“最多迭代次数”为1
   • Google Sheets：文件 > 设置 > 计算 > 迭代计算 > 勾选“允许迭代计算”，最大迭代次数设为1

2. 各日期列公式：

   • 项目新建日期（B2）：=IF(B2<>"", B2, IF(A2="New", NOW(), ""))
   • 项目上传日期（C2）：=IF(C2<>"", C2, IF(A2="Uploaded", NOW(), ""))
   • 项目完成日期（D2）：=IF(D2<>"", D2, IF(A2="Completed", NOW(), ""))

   原理：日期列已有值时直接保留；仅当日期列为空且状态匹配时，生成当前日期。迭代计算允许公式引用自身而不报错，次数限制为1可避免性能问题。

方案二：使用VBA宏（无循环引用，更稳定） #

如果不想启用迭代计算，VBA可在状态变更时自动记录日期并锁定，且日期不会随表格计算更新：

1. 右键工作表标签 > 查看代码，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅监控状态列（A列）的变更
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Dim rng As Range
        Set rng = Target
        ' 遍历变更的单元格（支持批量修改）
        For Each cell In rng
            Dim rowNum As Integer
            rowNum = cell.Row
            ' 根据状态更新对应日期列，仅当日期列为空时写入
            Select Case cell.Value
                Case "New"
                    If Cells(rowNum, "B").Value = "" Then
                        Cells(rowNum, "B").Value = Now()
                        ' 锁定单元格防止修改（可选）
                        Cells(rowNum, "B").Locked = True
                    End If
                Case "Uploaded"
                    If Cells(rowNum, "C").Value = "" Then
                        Cells(rowNum, "C").Value = Now()
                        Cells(rowNum, "C").Locked = True
                    End If
                Case "Completed"
                    If Cells(rowNum, "D").Value = "" Then
                        Cells(rowNum, "D").Value = Now()
                        Cells(rowNum, "D").Locked = True
                    End If
            End Select
        Next cell
    End If
End Sub

2. 保存文件为.xlsm格式（启用宏的工作簿），之后每次修改A列状态，对应日期列会自动写入当前日期，且仅写入一次。

方案三：使用隐藏辅助列（无需迭代/VBA，适合无宏环境） #

若不能使用宏或迭代计算，可添加隐藏列记录状态的首次触发标记：

1. 添加3个隐藏列（比如E、F、G），分别对应三个状态的触发标记：

   • E2（New触发标记）：=IF(A2="New", 1, E2)
   • F2（Uploaded触发标记）：=IF(A2="Uploaded", 1, F2)
   • G2（Completed触发标记）：=IF(A2="Completed", 1, G2)
     （首次设置时需手动确认初始值，避免公式清空已有标记）

2. 日期列公式：

   • B2：=IF(E2=1, IF(B2<>"", B2, NOW()), "")
   • C2：=IF(F2=1, IF(C2<>"", C2, NOW()), "")
   • D2：=IF(G2=1, IF(D2<>"", D2, NOW()), "")

   原理：隐藏列记录是否触发过对应状态，日期列仅在触发标记为1且自身为空时生成日期，后续状态变更时，触发标记保留1，日期列也保留已有值。

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