如何在VBA中累加超过24小时的时:分:秒格式时长？

问题背景 #

需要处理Sheet1中格式为hh:mm:ss的时长数据，累加后总和会超过24小时，要求以相同格式输出结果。现有VBA代码在处理跨天总时长（如100:35:20）时出现时间回滚，无法正确显示。

原代码如下：

standbyTime = CDate(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "U").Value)
failureTime = CDate(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "W").Value)
powerOffTime = CDate(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "X").Value)
                    
If (ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "W").Value <> "") Then
   operationalTime = TimeValue(failureTime) - TimeValue(standbyTime)
Else
   operationalTime = TimeValue(powerOffTime) - TimeValue(standbyTime)
End If
                    
totalTime = totalTime + operationalTime

问题原因 #

Excel中Time类型本质是小数数值，1代表完整的一天（24小时）。当累加的时长超过24小时，TimeValue或CDate会自动对24取模，只保留当天的时间部分，丢失了超过24小时的天数信息，导致显示错误。

解决方案 #

方法1：转换为总秒数累加，再格式化输出 #

将每个时长转换为总秒数进行累加，最后把总秒数转换为hh:mm:ss格式，这种方式能精准处理任意时长的累加。

修改后的代码示例：

Dim totalSeconds As Long
Dim standbySec As Long, failureSec As Long, powerOffSec As Long, operationalSec As Long

' 读取单元格值并转换为总秒数
standbySec = TimeToSeconds(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "U").Value)
If ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "W").Value <> "" Then
    failureSec = TimeToSeconds(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "W").Value)
    operationalSec = failureSec - standbySec
Else
    powerOffSec = TimeToSeconds(ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "X").Value)
    operationalSec = powerOffSec - standbySec
End If

' 累加总秒数
totalSeconds = totalSeconds + operationalSec

' 最后将总秒数转换为hh:mm:ss格式
Dim totalTimeStr As String
totalTimeStr = SecondsToTime(totalSeconds)
MsgBox "总时长：" & totalTimeStr

' 辅助函数：将hh:mm:ss字符串转换为总秒数
Function TimeToSeconds(timeStr As String) As Long
    Dim parts() As String
    parts = Split(timeStr, ":")
    TimeToSeconds = CLng(parts(0)) * 3600 + CLng(parts(1)) * 60 + CLng(parts(2))
End Function

' 辅助函数：将总秒数转换为hh:mm:ss格式
Function SecondsToTime(totalSec As Long) As String
    Dim hours As Long, mins As Long, secs As Long
    hours = totalSec \ 3600
    mins = (totalSec Mod 3600) \ 60
    secs = totalSec Mod 60
    SecondsToTime = Format(hours, "00") & ":" & Format(mins, "00") & ":" & Format(secs, "00")
End Function

方法2：用数值存储总时长（天为单位），自定义格式输出 #

如果需要保留Excel的时间数值特性（后续可继续计算），可以将总时长存储为以天为单位的数值，然后通过自定义单元格格式显示超过24小时的时长。

修改后的代码示例：

Dim totalTime As Double ' 用Double存储总时长（1=1天=24小时）
Dim operationalTime As Double

Dim standbyVal As String, failureVal As String, powerOffVal As String
standbyVal = ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "U").Value
failureVal = ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "W").Value
powerOffVal = ThisWorkbook.Worksheets("Sheet1").Cells(dateCell.Row, "X").Value

If failureVal <> "" Then
    ' 将时间字符串转换为天为单位的数值
    operationalTime = TimeValue(failureVal) - TimeValue(standbyVal)
Else
    operationalTime = TimeValue(powerOffVal) - TimeValue(standbyVal)
End If

totalTime = totalTime + operationalTime

' 将结果写入单元格，并设置自定义格式
With ThisWorkbook.Worksheets("Sheet1").Cells(1, "Z") ' 假设写入Z1单元格
    .Value = totalTime
    .NumberFormat = "[hh]:mm:ss" ' 方括号表示允许小时数超过24
End With

关键说明 #

• 方法1适合需要直接获取字符串格式结果的场景，不受Excel时间数值限制；
• 方法2适合需要保留数值可计算性的场景，通过[hh]:mm:ss自定义格式让Excel正确显示超过24小时的时长。

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