日期值相减产生错误 [英] Date value subtraction producing wrong error
本文介绍了日期值相减产生错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下代码以Day:Hour:Minute格式显示日期差.
I am using the below code to show the date difference in Day:Hour:Minute format.
Function TimeSpan(dt1, dt2)
Dim seconds,minutes,hours,days
If (isDate(dt1) And IsDate(dt2)) = false Then
TimeSpan = "00:00:00"
Exit Function
End If
seconds = Abs(DateDiff("S", dt1, dt2))
minutes = seconds \ 60
hours = minutes \ 60
days = hours \ 24
minutes = minutes mod 60
seconds = seconds mod 60
days = days mod 24
if len(hours) = 1 then hours = "0" & hours
TimeSpan = days& ":" & _
RIGHT("00" & hours , 2) & ":" & _
RIGHT("00" & minutes, 2)
End Function
但是在某些情况下它没有产生期望值.
But it is not producing expected values for some cases.
D1=#9/24/2012 8:09:15 AM# and D2=#9/25/2012 8:09:15 AM# gives correct data like 1:24:00 whereas below are producing error when working with VBScript and Excel.
D1=#9/5/2012 8:45:43 AM# and D2=#9/25/2012 8:45:43 AM# result=0.888888888888889
D1=#9/6/2012 8:29:34 AM# and D2=#9/17/2012 8:59:36 AM# result=0.503125
你能解释为什么吗?
谢谢
推荐答案
Try my answer from an earlier post in your UDF as the following: This answer is in VBA
请声明所有变量,并通过添加显式选项来强制自己声明:)
option explicit
Function TimeSpan(dt1 As Date, dt2 As Date) As String
Dim dtTemp As Date
Application.ScreenUpdating = False
If (IsDate(dt1) And IsDate(dt2)) = False Then
TimeSpan = "00:00:00"
Exit Function
End If
If dt2 < dt1 Then
dtTemp = dt2
dt2 = dt1
dt1 = dt2
End If
'-- since you only had days, I have put up to days here.
'-- if you require months, years you may use yy:mm:dd:hh:mm:ss
'-- which is pretty self-explainatory ;)
TimeSpan = Application.WorksheetFunction.Text((dt2 - dt1), "dd:hh:mm:ss")
Application.ScreenUpdating = False
End Function
UDF输出:
但是,如果您有足够的自由和可能性,我真的建议您使用Excel工作表功能.
然后根据本文使用解决方案
将DateDiff
合并到UDF中.
Then use the solution as per this article
Incorporate the DateDiff
to the UDF.
这篇关于日期值相减产生错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文