Minutes Between Given Date and "NOW" Across Multiple Dates

[himalayan]

Hi all,

I'm having some trouble working out how to do this; I've searched everywhere but as of yet no results have given the level of detail I require.

I need to calculate the difference in hours/minutes/seconds between a given date in the format: "29/06/2015 - 13:57:12" and the "NOW" function, however... to make the application clear; I'll give a brief description of the system I am trying to create:

1. Someone presses button "A". The date/time is recorded when the button is pressed, in the format: "dd/mm/yyyy - hh:mm:ss" i.e. 29/06/2015 - 14:01:12

2. I would like it so that when button "B" is pressed, the time that button "A" was pressed is given, as is the difference in hours/minutes/seconds between the time button "A" was pressed and the current time (i.e. 26:15:10, 26 hours, 15 minutes and 10 seconds)

I've tried everything but still cannot create a solution. If anyone can share any insight it would be much appreciated; thanks.

 

[baitmaster]

Hi, here is some code that will help you, you should be able to adapt this to meet your needs

Option Explicit

Public timerStart, timerEnd

Sub setTimeStart(Optional strRoutine As String)
timerStart = Now()
Debug.Print strRoutine & " routine started at " & timerStart
End Sub

Sub setTimeEnd(Optional strRoutine As String)
timerEnd = Now()
Debug.Print strRoutine & " routine ended at " & timerEnd
Debug.Print " duration " & Format(timerEnd - timerStart, "hh:mm:ss")
Debug.Print " ------"
MsgBox "duration " & Format(timerEnd - timerStart, "hh:mm:ss")
End Sub

 

[ranman256]

on btnA_click
cell E2 = NOW()

on btnB_click
cell F2 = NOW()
cell G2 = DATEDIFF("s",E2,F2)

then run it thru this translator

'USAGE:  TimeElapsedAsTextRecur(655)
Public Function TimeElapsedAsTextRecur(ByVal pvSecs, Optional ByVal pvSecBlock)
'recursive time lapse given seconds
Dim vTxt
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 = "years"
          If iNum > 0 Then
               vTxt = iNum & " years "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 2592000)
          
      Case 2592000    'MO
          sUnit = "months"
          If iNum > 0 Then
               If iNum > 11 Then iNum = 11
               vTxt = vTxt & iNum & " months "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 604800)
       
       Case 604800     'WEEK
          sUnit = "weeks"
          If iNum > 0 Then
               If iNum > 3 Then iNum = 3
               vTxt = vTxt & iNum & " weeks "
               pvSecs = pvSecs - (iNum * kDAY * 7)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 86400)
       
       Case kDAY      'day
          sUnit = "days"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " days "
               pvSecs = pvSecs - (iNum * kDAY)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 3600)
       
       Case 3600       'hrs
          sUnit = "hrs"
          If iNum > 23 Then iNum = 23
          If iNum > 0 Then
               vTxt = vTxt & iNum & " hrs "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 60)
       
       Case 60         'min
          sUnit = "mins"
          If iNum > 0 Then
               vTxt = vTxt & iNum & " mins "
               pvSecs = pvSecs - (iNum * pvSecBlock)
          End If
          vTxt = vTxt & TimeElapsedAsTextRecur(pvSecs, 1)
       
       Case Else
          
          sUnit = "secs"
          If pvSecs > 0 Then vTxt = vTxt & pvSecs & " seconds"
    End Select
    
TimeElapsedAsTextRecur = vTxt
End Function

 

[himalayan]

Thanks both!