Calculating time difference between two "NOW()" timestamps

[vbaBaby]

Hi,

I've created a userform where people capture information and have to time the process for the work that they are doing. The information is then transferred to the worksheet and the data moves down the next row. using the following code:

Range("A10:V10").SelectRows(10).Insert shift:=xlShiftDown
firstrow = ActiveCell.Row
Cells(firstrow + 1, 1).Value = UserTextBox.Text
Cells(firstrow + 1, 2).Value = Company.Text
Cells(firstrow + 1, 3).Value = CurrentDate.Text
Cells(firstrow + 1, 4).Value = DateReceived.Text
Cells(firstrow + 1, 5).Value = TimeReceived.Text
Cells(firstrow + 1, 6).Value = DateSignOff.Text
Cells(firstrow + 1, 7).Value = Requestor.Text
Cells(firstrow + 1, 8).Value = BeginTimeBox.Text
Cells(firstrow + 1, 9).Value = EndTime
Cells(firstrow + 1, 10).Value = WorkCategory.Text
Cells(firstrow + 1, 11).Value = ActivityTextBox.Text
Cells(firstrow + 1, 12).Value = InHouseBox.Text
Cells(firstrow + 1, 13).Value = Path.Text
Cells(firstrow + 1, 14).Value = Skill.Text
Cells(firstrow + 1, 15).Value = Validity.Text
Cells(firstrow + 1, 16).Value = CommentBox.Text
Cells(firstrow + 1, 17).Value = ManualTime.Text
Cells(firstrow + 1, 18).Value = PausedTimeBox.Text
Cells(firstrow + 1, 19).Value = ContinuedTimeBox.Text
Cells(firstrow + 1, 20).Value = TimeSignOff.Text
Cells(firstrow + 1, 21).Value = ""
Cells(firstrow + 1, 22).Value = Duration

I'm trying to use the timer option to calculate the time taken to do a process. I've used an the NOW() function to enter the different times which gets added clicking start, pause, continue and stop buttons.

Private Sub StartButton_Click()'Inserts the start time and date
'Start time function
Dim BeginTime As Date
BeginTime = Format(Now(), "hh:mm:ss")


StatsCapture.BeginTimeBox.Value = BeginTime


End Sub

Private Sub PauseButton_Click()
'Inserts the pause time
'Pause time function


'Checks if timer has been started
If StatsCapture.BeginTimeBox.Value = "" Then
    MsgBox "Timer has not been started!"
    Exit Sub
    End If
    
Dim PausedTime As Date
PausedTime = Format(Now(), "hh:mm:ss")


StatsCapture.PausedTimeBox.Value = PausedTime


End Sub

Private Sub ContinueButton_Click()
'Inserts the pause time
'Pause time function


'Checks if timer has been started
If StatsCapture.BeginTimeBox.Value = "" Then
    MsgBox "Timer has not been started!"
    Exit Sub
    End If


'Checks if timer has been paused
If StatsCapture.PausedTimeBox.Value = "" Then
    MsgBox "Timer is not paused!"
    Exit Sub
    End If
    
Dim ContinuedTime As Date
ContinuedTime = Format(Now(), "hh:mm:ss")


StatsCapture.ContinuedTimeBox.Value = ContinuedTime


End Sub

'Returns end time when button is pressed
Dim EndTime As Date
EndTime = Format(Now(), "hh:mm:ss")

'Calculates the time taken for the process
Dim Duration As Double
Duration = EndTime + PauseTime - StartTime - ContinueTime

The problem is that when it does the calculation it returns the time "as in a actual clock time" - if that makes sense - instead of the time difference in hours, minutes or seconds taken from when the start button and stop buttons were click, also if the pause and continue buttons where clicked. is there a way that i can do this calculation in VBA and then export this to the worksheet.

I don't know where I'm going wrong, please help.

 

[oldbrewer]

what is actual clock time - is it 0.5 = 12 hours etc ?

 

[vbaBaby]

It comes up as the time as in 12:01:05 which is not correct when the process was running for only 5mins. For instance I started the process at 10:06:27, paused it at 10:18:29, continue at 10:18:38 and ended at 10:18:41. it returned 10:18:41.

 

[oldbrewer]

i am not that good with macros but I see you have a begintime, then a pausedtime, but cannot see where your restart time and end time are

 

[joeu2004]

It comes up as the time as in 12:01:05 which is not correct when the process was running for only 5mins. For instance I started the process at 10:06:27, paused it at 10:18:29, continue at 10:18:38 and ended at 10:18:41. it returned 10:18:41.

Probably because in the context of the following statement:

Duration = EndTime + PauseTime - StartTime - ContinueTime

PauseTime, StartTime and ContinueTime are as-yet unused variables, and their default value is zero.

(Also note that you wrote PauseTime and ContinueTime here, but PausedTime and ContinuedTime elsewhere. And you wrote StartTime here, but BeginTime elsewhere.)

I suggestion that you add the statement Option Explicit at the top of each module and class in order to catch such mistakes.

I suspect your intent is to refer to the variables with the same names that are in the separate "Click" event macros. But those variables are local to their scope (Sub).

Perhaps the following meets your needs. Note several improvements, among them being:

1. Allow the user to pause and continue multiple times.
2. Ignore the "continue" click if the user did not pause. (I see no harm.)
3. Allow the user to stop after pause without clicking "continue" first. (Ease of use.)
4. Also see explanation of Now usage below.

Option Explicit

' use Private instead of Public if variables are not accessed in other modules
Public BeginTime As Date
Public PausedTime As Date
Public Duration As Double

Private Sub StartButton_Click()  ' Inserts the start time and date
    ' if already started, ignore click silently (user-friendly)
    If BeginTime <> 0 Then Exit Sub

    BeginTime = Now
    
    ' not necessary; retained in case you want it for other reasons
    StatsCapture.BeginTimeBox.Value = Format(BeginTime, "hh:mm:ss")
End Sub

Private Sub PauseButton_Click()  ' Inserts the pause time
    ' nothing running; ignore click silently (user-friendly)
    If BeginTime = 0 Then Exit Sub
    
    ' already paused; ignore click silently (user-friendly)
    If PausedTime <> 0 Then Exit Sub
    
    PausedTime = Now
    
    ' not necessary; retained in case you want it for other reasons
    StatsCapture.PausedTimeBox.Value = Format(PausedTime, "hh:mm:ss")
End Sub

Private Sub ContinueButton_Click()  ' Inserts the pause time
    Dim ContinuedTime As Date       ' local variable, not used elsewhere
    
    ' nothing paused; ignore click silently (user-friendly)
    If PausedTime = 0 Then Exit Sub
    
    ' accumulate multiple pause times
    ContinuedTime = Now
    Duration = Duration + ContinuedTime - PausedTime
    
    ' allow for another pause later
    PausedTime = 0
    
    ' not necessary; retained in case you want it for other reasons
    StatsCapture.ContinuedTimeBox.Value = Format(ContinuedTime, "hh:mm:ss")
End Sub

' these statements were out of context,
' but you said there is a stop button

Private Sub StopButton_Click()  ' inserts end time when button is pressed
    Dim EndTime As Date         ' local variable, if not used elsewhere
    
    ' nothing running; ignore click silently (user-friendly)
    If BeginTime = 0 Then Exit Sub

    ' end pause, if any
    If PausedTime <> 0 Then Call ContinueButton_Click

    EndTime = Now

    ' subtract any accumulated pause time
    Duration = EndTime - BeginTime - Duration
    
    ' allow for restarting later (?)
    BeginTime = 0

    [.... other code ....]
End Sub

Note 1: If you just want time of day, use Time instead of Now. However, Now is beneficial if the start and end times span two or more days (e.g. start before midnight and end afterwards). In your original implementation, you lost the benefit of Now by using only the formatted time.

Note 2: Technically, the following is more reliable. It ensures that time is rounded to the second. Otherwise, binary arithmetic might introduce an infinitesimal difference, which might impact your use of the calculated duration elsewhere.

Duration = WorksheetFunction.Round((EndTime - StartTime - Duration)*86400, 0) / 86400

As a habit, I use WorksheetFunction.Round instead of VBA Round because the latter rounds differently in some circumstances. Admittedly, it is not likely to be a problem here.