代码之家 › 专栏 › 技术社区 › Przemyslaw Remin

vba中两个日期之间的间隔

excel-vba vba excel

-1

Przemyslaw Remin · 技术社区 · 6 年前

开始时间 "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

但它计算了几个小时的错误结果。真想不到! 你能分享一下计算预期结果的建议吗?

6 回复 | 直到 5 年前

Chronocidal 6 年前

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

在您的示例中,d2-d1是日期时间 1899-12-30 08:37:00 -将其格式设置为小时(2位)、小数点、分钟(2位),然后转换为双精度并更正符号。

MichaÅ Turczyn 6 年前

试试这个:

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

Dy.Lee 6 年前

尝试

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

Vityata 6 年前

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中的日期显示为一个双数字。因此,如果您编写0.373611111111111并将其格式化为vba中的日期,您将得到:

?CDate(0.373611111111111)
08:58:00

上面的双精度值是通过在 即时窗口 Ctrl键 + G :

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

在Excel中,日期也显示为两个数字,但在一年的前两个月 一千九百 它有点偏离了vba中的日期(参见 BillG here )

-1

Harassed Dad 6 年前

或者-假定时间存储为一天的小数部分

 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

-1

Gene 6 年前

一般的答案是:

      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

等等。(有点复杂,只需几分钟)