How to calculate time difference in hours, minutes and seconds?

[MsAxes]

Hello all I have two fields in my table, [EnterTime] and [LeaveTime]. Both fields have hours, minutes and seconds and I would like to calculate the difference in hours, minutes and seconds as well. I have the expression below which returns the difference in hours and minutes but not seconds which I need.



I did change the "n" to an "s" and (24*60) to (1440*60) but the results were the same.

FormatDateTime((DateDiff("n",([EnterTime],[LeaveTime]))/(24*60),4)

Thoughts?

[ranman256]

paste this code into a module,
then usage is:
sElapsed = CalcElapsedTimeAsTxt(txtDate1, txtDate2)

Public Function CalcElapsedTimeAsTxt(ByVal pvDate1, ByVal pvDate2)
Dim lSecs As Long
lSecs = DateDiff("s", pvDate1, pvDate2)
CalcElapsedTimeAsTxt = ElapsedTimeAsTextRecur(lSecs)
End Function


'USAGE:  ElapsedTimeAsTextRecur(655)
Public Function ElapsedTimeAsTextRecur(ByVal pvSecs, Optional ByVal pvSecBlock)
'recursive time lapse given seconds
Dim vTxt, sUnit
Dim iNum As Long
Const kDAY = 86400
Const kSECpYR = 31536000


'60 sec = 1 min             60 sec
'60 min = 1 hr            3600 sec
'24 hr = 1 day           86400 sec
'7 days = 1 week        604800 sec
'30 days = 1 month     2592000
'12 months = 1 year = 31536000


'YEARS
If IsMissing(pvSecBlock) Then pvSecBlock = kSECpYR
iNum = pvSecs \ pvSecBlock
    
    Select Case pvSecBlock
       Case kSECpYR   'yr
          sUnit = "year"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " " & sUnit
               GoSub FixPlural
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 2592000)
          
      Case 2592000    'MO
          sUnit = "month"
          If iNum > 0 Then
               If iNum > 11 Then iNum = 11
               vTxt = vTxt & iNum & " " & sUnit
               GoSub FixPlural
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 604800)
       
       Case 604800     'WEEK
          sUnit = "week"
          If iNum > 0 Then
               If iNum > 3 Then iNum = 3
               vTxt = vTxt & iNum & " " & sUnit
               GoSub FixPlural
               pvSecs = pvSecs - (iNum * kDAY * 7)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 86400)
       
       Case kDAY      'day
          sUnit = "day"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " " & sUnit
               GoSub FixPlural
               pvSecs = pvSecs - (iNum * kDAY)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 3600)
       
       Case 3600       'hrs
          sUnit = "hr"
          If iNum > 23 Then iNum = 23
          If iNum > 0 Then
               vTxt = vTxt & iNum & " " & sUnit
               GoSub FixPlural
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 60)
       
       Case 60         'min
          sUnit = "min"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " min "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 1)
       
       Case Else
          
          sUnit = "secs"
          'If pvSecs > 0 Then vTxt = vTxt & pvSecs & " seconds"
    End Select
    
ElapsedTimeAsTextRecur = vTxt
Exit Function


FixPlural:
If iNum > 1 Then vTxt = vTxt & "s " Else: vTxt = vTxt & " "
Return
End Function

[CJ_London]

4 is shorttime (hours and minutes) you need to use 3 (vblongtime) for hours, minutes and seconds

you also seem to have extra brackets and more ( than ) try

FormatDateTime(DateDiff("s",[EnterTime],[LeaveTime])/1440*60,3)

also be aware the if enter time is before midnight and leave time after, you will get a wrong result

edit

assuming entertime and leavetime are datetime field just

FormatDateTime(LeaveTime-EnterTime,3)

if midnight is an issue

FormatDateTime(abs(LeaveTime<EnterTime)+LeaveTime-EnterTime,3)

[MsAxes]

Ajax, your 1st option is close except when the hour entry hour is 3:00:00 pm for example and the leave time is the same hour 3:05:04 pm(doesn't happen often) the result is 12:05:04 AM.

[CJ_London]

missed the brackets
1440*60=86400

?FormatDateTime(DateDiff("s",#3:00 PM#,#3:05:04 PM#)/86400,3)
00:05:04
?FormatDateTime(#3:05:04 PM#-#3:00 PM#,3)
00:05:04

[MsAxes]

Thanks Ajax, that did it.