Excel任务耗时追踪：状态变更后恢复计时（公式或VBA实现）

一、公式方案（无需VBA） #

首先建议调整表格结构，新增Status Change Date列记录每次状态切换的时间（原Start Date保留为任务首次启动时间，End Date仅在状态为Accepted时填写最终完成时间），优化后结构如下：

适用Excel 365/2021（动态数组公式） #

在Elapsed Time列的对应单元格（如F2）输入以下公式，按回车即可自动计算该任务的累计耗时：

=LET(
    taskRows, FILTER($A$2:$E$6, $A$2:$A$6=A2),
    statusList, INDEX(taskRows,,3),
    dateList, INDEX(taskRows,,4),
    endDate, XLOOKUP("Accepted", statusList, INDEX(taskRows,,5)),
    timeIntervals, IF(
        statusList="In analysis",
        IFERROR(INDEX(dateList, MATCH(1, (statusList<>"In analysis")*(ROW(dateList)>ROW(dateList)), 0))-dateList,
        IF(statusList="Accepted", endDate-dateList, 0)),
    0),
    SUM(timeIntervals)
)

适用旧版Excel（数组公式） #

输入以下公式后，按Ctrl+Shift+Enter确认生效：

=SUM(IF(($A$2:$A$6=A2)*($C$2:$C$6="In analysis"), IFERROR(INDEX($D$2:$D$6, MATCH(1, ($A$2:$A$6=A2)*(ROW($D$2:$D$6)>ROW(D2)), 0))-$D$2:$D$6, IF($C2="Accepted", $E2-$D2, 0)), 0))

最后将Elapsed Time单元格格式设置为h:mm:ss，即可显示正确的耗时格式。

二、VBA自动处理方案 #

若需要自动触发计算，可添加工作表Change事件，当状态或日期变更时自动更新耗时：

1. 右键目标工作表标签 → 选择「查看代码」，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim taskID As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim totalTime As Double
    Dim analysisStart As Date
    
    Set ws = Target.Parent
    ' 仅监听Status列（C列）、状态变更日期列（D列）、结束日期列（E列）的变更
    If Intersect(Target, ws.Range("C:C,D:D,E:E")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    taskID = ws.Cells(Target.Row, "A").Value
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    totalTime = 0
    analysisStart = 0
    
    ' 遍历当前任务的所有状态记录
    For i = 2 To lastRow
        If ws.Cells(i, "A").Value = taskID Then
            Select Case ws.Cells(i, "C").Value
                Case "In analysis"
                    analysisStart = ws.Cells(i, "D").Value
                Case "Returned", "Accepted"
                    If analysisStart <> 0 Then
                        totalTime = totalTime + (ws.Cells(i, "D").Value - analysisStart)
                        analysisStart = 0
                    End If
                    ' 状态为Accepted时自动填充End Date（若未填写）
                    If ws.Cells(i, "C").Value = "Accepted" Then
                        If ws.Cells(i, "E").Value = "" Then
                            ws.Cells(i, "E").Value = ws.Cells(i, "D").Value
                        End If
                        ' 处理直接从In analysis切换到Accepted的情况
                        If analysisStart <> 0 Then
                            totalTime = totalTime + (ws.Cells(i, "E").Value - analysisStart)
                        End If
                    End If
            End Select
        End If
    Next i
    
    ' 更新当前任务所有行的Elapsed Time
    For i = 2 To lastRow
        If ws.Cells(i, "A").Value = taskID Then
            ws.Cells(i, "F").Value = totalTime
            ws.Cells(i, "F").NumberFormat = "h:mm:ss"
        End If
    Next i
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

2. 将工作簿保存为.xlsm格式（启用宏的工作簿），之后修改状态或日期时，Elapsed Time会自动更新。

逻辑说明 #

• 公式方案：筛选同一任务的所有记录，识别所有In analysis时段，计算每个时段到下一次状态变更的时间差并累加求和。
• VBA方案：监听单元格变更事件，跟踪In analysis的开始时间，遇到Returned或Accepted时计算时段时长并累加，最终自动更新累计耗时。

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