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

89 1

``````
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

``````

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

``````
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

``````

``````
?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")

``````

``````
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)

``````

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

``````