excel - 在VBA中,两个日期之间的excel间隔

  显示原文与译文双语对照的内容
89 1

开始时间 "08:58",结束时间 "17:37" 。 预期结果为 8.39表示 8小时和 39分钟经过的时间。 我试过这个:


Function UDF_datediff(d1 As Date, d2 As Date) As Double


 Dim hours As Integer


 Dim minutes As Integer



 hours = DateDiff("h", d1, d2)


 minutes = (DateDiff("n", d1, d2) Mod 60)



 UDF_datediff = hours + minutes/100


End Function



但它计算错误的结果数小时。 令人惊讶你是否愿意分享一些建议来计算预期结果? !

时间:

116 2

Function UDF_datediff(d1 As Date, d2 As Date) As Double


 Dim difference AS Double


 difference = d2 - d1


 UDF_datediff = Sgn(difference) * CDbl(Format(difference,"hh.mm"))


End Function



在你的示例中,是 1899-12-30 08:37:00的日期时间,格式为小时( 2位数字),小数点,分钟( 2位数字),然后更正标志。

83 3

尝试这个:


Sub Test()


 Dim minutes As Long


 Dim d1 As Date, d2 As Date


 d1 ="2018-01-01 08:58:00"


 d2 ="2018-01-01 17:37:00"


 minutes = DateDiff("n", d1, d2)


 ' Will print 8.39


 MsgBox Int(minutes/60) &"." & minutes Mod 60


End Sub



原作者:
55 4

try


Sub test()


 Dim d1 As Date, d2 As Date


 Dim m As Double



 d1 = TimeValue("08:58")


 d2 = TimeValue("17:37")


 m = UDF_datediff(d1, d2)


 MsgBox m


 MsgBox Format(m/24,"hh:mm")


End Sub



UDF


Function UDF_datediff(d1 As Date, d2 As Date) As Double



 UDF_datediff = (d2 - d1) * 24


End Function



141 1

实际上不需要 DateDiff 。 你可以使用一个简单的"减号"来查看发生了什么:


Sub TestMe() 



 With Worksheets(1)


 Dim d1 As Date: d1 = 0.373611111111111 '08:58:00


 Dim d2 As Date: d2 = 0.734027777777778 '17:37:00


 End With


 Debug.Print UdfDatediff(d1, d2) '08:39:00



End Sub



Function UdfDatediff(d1 As Date, d2 As Date) As Date



 UdfDatediff = Abs(d1 - d2)



End Function



VBA中的日期表示为双精度数。 因此,如果在VBA中编写 0.373 611111111111并将它的格式化为日期,则会得到:


?CDate(0.373611111111111)


08:58:00



上面的两个值通过在即时窗口 Ctrl+G中写入以下内容获得:


?CDbl(TimeSerial(8,58,0))


 0,373611111111111 



Excel中的日期也以双数字表示,但在年前两个月中,这与 VBA ( 请看这里的 BillG问题。) 中的日期有一点偏差。

原作者:
113 2

alternatively - 给定时间被存储为一天的小数分数


 Function UDF_datediff(d1 As Date, d2 As Date) As Double


 d2 = d2-int(d2) 'remove date portion


 d1= d1 - int(d1)


 UDF_datediff = (d2-d1)*24


 End Function



66 1

一般的答案是:


 Format(CDate(d2-d1),"h:m")



特别是对于开始和结束时间,这个表达式将产生" 8: 39"结果。

如果需要从中提取小时数和分钟数,则可以执行以下操作:


Dim sDuration as String


Dim iCol as Long


Dim sH as String


Dim sM as String


Dim iH as Long


Dim iM as Long



sDuration = Format(CDate(d2-d1),"h:m") ' the"8:39" in your case


iCol =InStr(sDuration,":") 


sH = Left(sDuration, iCol - 1)


sM = Mid(sDuration, iCol + 1, 2) ' length of 2 or any longer


iH = CInt(sH)


iM = CInt(sM)



你始终可以使用"数字数",转换小数,乘以 24和 60,舍入。 如中


iH = Int(CDate(d2-d1)*24) ' to get the hours



等等( 几分钟后就更复杂了)

原作者:
...