VBA 计算两个时间戳之间有多少天、几小时、几分钟或几秒?

Question

我正在尝试使用 vba 显示两个时间戳之间的天数、小时数、分钟数或秒数。

到目前为止，我的代码给了我小时数或天数。

Sub Date_Dif()
Dim d1 As Date
d1 = Range("A8").Value
Dim d2 As Date
d2 = Now
Dim hrsDiff As Long
hrsDiff = DateDiff("h", d1, d2)
ActiveSheet.Range("Z5").Value = IIf(hrsDiff >= 24, _
hrsDiff  24 & " days " & hrsDiff Mod 24 & " hours", _
hrsDiff & " hours")
End Sub

我怎样才能调整它以给我几分钟或几秒钟？

提前致谢

Answer 1

最简单的方法是减去日期并使用Hour、Minute和Second函数。 由于Date变量存储为Double，整数部分表示自纪元以来的天数，因此您可以直接将其用于减法后的天数：

Dim diff As Date
diff = Now - #7/7/2016 2:15:16 PM#
Debug.Print CLng(Int(diff)) & " days"
Debug.Print Hour(diff) & " hours"
Debug.Print Minute(diff) & " minutes"
Debug.Print Second(diff) & " seconds"

把它插入你的Sub，你会得到这样的东西：

Sub Date_Dif()
Dim d1 As Date
d1 = Range("A8").Value
Dim diff As Date
diff = Now - d1
ActiveSheet.Range("Z5").Value = _
CLng(Int(diff)) & " days " & _
Hour(diff) & " hours " & _
Minute(diff) & " minutes " & _
Second(diff) & " seconds"
End Sub