计算超过24小时的时差 [英] Calculating Time Difference Exceeding 24 hours
问题描述
我遇到一个问题,我试图以秒为单位计算时间差,然后在报告(访问报告)中将这些秒相加并将其格式化为hh:nn:ss.
I am having an issue where I am trying to calculate the time difference in seconds and then in a report (Access reports) I will sum those seconds and format it into hh:nn:ss.
但是,我的计算出的包含两个字段之间的时差的字段有时会超过24小时,因此会抛出该时差.
However, my calculated field that gathers the time difference between the two fields sometimes exceeds 24 hours and thus throws off the time difference.
我正在使用DateDiff函数--- DateDiff("s",[BeginningTime],[EndingTime])
I am using the DateDiff function --- DateDiff("s",[BeginningTime],[EndingTime])
当时间超过24小时时,我该怎么办?
What should I do when it comes to circumstances where the time exceeds 24 hours?
两个字段BeginningTime和EndingTime以AM/PM格式存储.我认为这无关紧要.
The two fields, BeginningTime and EndingTime, are stored in the AM/PM format. I don't think that should matter though.
推荐答案
您可以使用以下函数:
Public Function FormatHourMinute( _
ByVal datTime As Date, _
Optional ByVal strSeparator As String = ":") _
As String
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
' datTime: #10:03# + #20:01#
' returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.
Dim strHour As String
Dim strMinute As String
Dim strHourMinute As String
strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
' Add leading zero to minute count when needed.
strMinute = Right("0" & CStr(Minute(datTime)), 2)
strHourMinute = strHour & strSeparator & strMinute
FormatHourMinute = strHourMinute
End Function
,此表达式为您的文本框的 ControlSource :
and this expression as ControlSource for your textbox:
=FormatHourMinute([EndingTime]-[BeginningTime])
但是(请参阅注释),该简单表达式仅对正数值的日期有效,该日期是1899-12-30之后的日期.
However (see comments) this simple expression is only valid for dates of positive numeric value which are dates after 1899-12-30.
要涵盖所有日期,您将需要一种正确的计算时间跨度的方法,可以使用以下功能来完成此操作:
To cover all dates, you will need a proper method for calculating a timespan, and that can be done using this function:
' Converts a date value to a timespan value.
' Useful only for date values prior to 1899-12-30 as
' these have a negative numeric value.
'
' 2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateToTimespan( _
ByVal Value As Date) _
As Date
ConvDateToTimespan Value
DateToTimespan = Value
End Function
' Converts a date value by reference to a linear timespan value.
' Example:
'
' Date Time Timespan Date
' 19000101 0000 2 2
'
' 18991231 1800 1,75 1,75
' 18991231 1200 1,5 1,5
' 18991231 0600 1,25 1,25
' 18991231 0000 1 1
'
' 18991230 1800 0,75 0,75
' 18991230 1200 0,5 0,5
' 18991230 0600 0,25 0,25
' 18991230 0000 0 0
'
' 18991229 1800 -0,25 -1,75
' 18991229 1200 -0,5 -1,5
' 18991229 0600 -0,75 -1,25
' 18991229 0000 -1 -1
'
' 18991228 1800 -1,25 -2,75
' 18991228 1200 -1,5 -2,5
' 18991228 0600 -1,75 -2,25
' 18991228 0000 -2 -2
'
' 2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub ConvDateToTimespan( _
ByRef Value As Date)
Dim DatePart As Double
Dim TimePart As Double
If Value < 0 Then
' Get date (integer) part of Value shifted one day
' if a time part is present as -Int() rounds up.
DatePart = -Int(-Value)
' Retrieve and reverse time (decimal) part.
TimePart = DatePart - Value
' Assemble date and time part to return a timespan value.
Value = CDate(DatePart + TimePart)
Else
' Positive date values are identical to timespan values by design.
End If
End Sub
然后您的表情将如下所示:
Then your expression will look like:
=FormatHourMinute(DateToTimespan([EndingTime])-DateToTimespan([BeginningTime]))
对于Gord的示例值#1899-12-28 01:00:00#
和#1899-12-27 23:00:00#
,将返回2:00.
which for Gord's example values, #1899-12-28 01:00:00#
and #1899-12-27 23:00:00#
, will return 2:00.
这篇关于计算超过24小时的时差的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!