如何在Excel中正确地将秒转换为hh：mm：ss

I am trying to calculate the time it takes to complete an analysis in Excel. I'm using DateDiff to calculate the number of seconds it takes and then try to format it in hours, minutes, seconds.

我正在尝试计算在Excel中完成分析所需的时间。我正在使用DateDiff计算它所需的秒数，然后尝试以小时，分钟，秒为格式。

My code below causes an overflow error on line:

我的代码在下面导致溢出错误：

dTime = dTime / (60 * 60 * 24)

Do you know what I am doing wrong?

你知道我做错了什么吗？

Dim dTime As Long
Dim startTime, endTime As Date

startTime = Now() ' at the start of the analysis
endTime = Now() ' at the end of the analysis

dTime = DateDiff("s", startTime, endTime)
dTime = dTime / (60 * 60 * 24) 'convert seconds into days
StatusBox.Value = "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

2 个解决方案

#1

It seems that you are over-compensating. No conversion is necessary. Simply subtract the start time from the end time (optionally stored as a double as mentioned by @MatthewD) and format the cell as hh:mm:ss (preferred) or as a string representing time with Format(dTime, "hh:mm:ss").

看来你过度补偿了。无需转换。简单地从结束时间中减去开始时间（可选地存储为@MatthewD提到的双精度格式）并将单元格格式化为hh：mm：ss（首选）或格式化为表示格式时间的字符串（dTime，“hh：mm： SS“）。

Dim dTime As Double
Dim startTime As Date, endTime As Date

startTime = Now() ' at the start of the analysis
'analysis here
endTime = Now() ' at the end of the analysis

dTime = endTime - startTime 
StatusBox.Value = "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

Your startTime declaration was incorrect if you wanted it to be a Date type var. The method you were using created it as a variant.

如果您希望它是Date类型var，则startTime声明不正确。您使用的方法将其创建为变体。

#2

Try this

尝试这个

Sub seconds()
Dim dTime As Variant
Dim startTime, endTime As Date

startTime = #8/7/2015 10:43:32 PM# ' at the start of the analysis
endTime = Now() ' at the end of the analysis

dTime = DateDiff("s", startTime, endTime)
dTime = dTime / 86400
MsgBox "Analysis completed in " & Format(dTime, "hh:mm:ss") & "."

End Sub