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

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

时间:原作者:0个回答

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

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

原作者:
...