Calulating Elapsed Time

Topic

New Reply

Can anyone provide me with some code to make a label on a userform display the elapsed time? I would appreciate references to “start” and “stop” buttons.

The plan is to use the following line to tie it to my worksheet…

Timebox.Value = Range(“a” & i).Value
‘where i is an incremental variable representing row number.

Thanks Millions!
Drk

Reply | Quote

Replies

You didn’t say what version of Excel you have. The following works in Excell 2000:

Timebox.Value=Application.WorksheetFunction.Text(ActiveSheet.Cells(i, 1).Value, "[h]:mm")

Reply | Quote

Unfortunately, it’s Excel ’97…

I’m having no trouble displaying the current time, I would like to create a stopwatch on the form, which would send the value to a worksheet. I.E. User clicks on start, time begins to elapse, user clicks on stop, time halts, user clicks on submit, and time is submitted to worksheet. I’ve already got the submission part down, it’s generating the stopwatch i’m having issues with…

Thanks for the reply Legare!

Reply | Quote

Did you try it? It might work in Excel 97, but I can’t try it. If it doesn’t work, try it without the “Application.Worksheetfunction” and see if that works. If that does not work, tell me what error you get in both cases.

Reply | Quote

It returned a type mismatch error in the context you provided, when removing the Application.Worksheetfunction, VBA goes red.. (Doesn’t like the Syntax)

I can display the time by using the following code:

timebox.value = time()

i’m looking for a way to elapse time in an object, starting at 0:00:00 any ideas?

Thanks again!
Drk

Reply | Quote

OK, then the following should work:

Dim stW As String
    stW = Int(ActiveSheet.Cells(i, 1) * 24) & ":"
    stW = stW & Int((ActiveSheet.Cells(i, 1) * 24 - Int(ActiveSheet.Cells(i, 1) * 24)) * 60) & ":"
    stW = stW & Int((ActiveSheet.Cells(i, 1) * 1440 - Int(ActiveSheet.Cells(i, 1) * 1440)) * 60)
    Timebox.Value = stW

Reply | Quote

Still getting Type Mismatch errors… Is this code intended for Excel 2000?

What code would I use to start/define a timer event to capture the running time upon btnstarttime_click?

Thanks!

Reply | Quote

Hi,
Sorry to butt in, but I have some code that I think does something similar to what you want. This is based on a userform with a textbox called txtTimeElapsed and one commandbutton called cmdRun:
Private Sub cmdRun_Click()
Static blnStarted As Boolean, dteStarted As Date, dteStopped As Date
If blnStarted = False Then
dteStarted = Now()
Me.cmdRun.Caption = “Stop”
Else
dteStopped = Now()
Me.cmdRun.Caption = “Start”
Me.txtTimeElapsed = Format(dteStopped – dteStarted, “hh:mm:ss”)
End If
blnStarted = Not blnStarted
End Sub
There’s only one button because it functions as a start/stop button.
Is that any use?

Reply | Quote

That’s it!

Can you think of any way to show the time as it is elapsing?

Thanks!

Reply | Quote

Took a bit of thought, but yes! Change the code to:
Private Sub cmdRun_Click()
Static blnStarted As Boolean, dteStarted As Date, dteStopped As Date
If blnStarted = False Then
dteStarted = Now()
Me.cmdRun.Caption = “Stop”
Else
dteStopped = Now()
With Me
.cmdRun.Caption = “Start”
.txtTimeElapsed = Format(dteStopped – dteStarted, “hh:mm:ss”)
End With
End If
blnStarted = Not blnStarted
Do Until blnStarted = False
Me.txtTimeElapsed = Format(Now() – dteStarted, “hh:mm:ss”)
DoEvents
Loop
End Sub
and you can watch the seconds tick away….

Reply | Quote

Alas!! that’s amazing!

Almost there… i’m noticing that when I click on the stop button at times, it doesn’t respond, is this due to the loop? is there anything I can do to make it more ‘responsive’?

Reply | Quote

Also… (I’m a picky one today..)

At times, users will have to add callback time to the equation, I can setup a seperate button/field for callback time and add the values for total time, but in the interests of efficiency, is there any way to call the previous value and resume time from there?

Also, i’m having problems making my text boxes recognize time format… any ideas? I.E. they’re returning the .34351etc value excel uses to determine time.

THANKS MILLIONS!

Drk

Reply | Quote

Text boxes display text, they don’t know about formatting. If you assign a numeric value to a text box, VBA will just give you the default “General” format. If you want the value formated as a time, you have to do the formatting into a string and put the formatted string into the box.

Reply | Quote

Legare,

Can you elaborate on this a little more? How would I put the formatting into a string?

Thanks!
Drk.

Reply | Quote

Ruh-Roh….

It seems that due to the loop, I am unable to type any information to my other fields… I can call the time, store it, add it to another time, etc… but while the clock is running, I can’t really do anything. even stopping the clock is a challenge… any ideas?

Thanks!
Drk.

Reply | Quote

Ah! I don’t think you’ll be able to achieve that using the method I suggested – sorry, I didn’t realise initially that you were going to be doing other things at the same time!
Doing it this way, you won’t be able to have the ‘clock’ changing, as it’s the loop that’s stopping your inputting.
If you take the loop out, you can still calculate the elapsed time though, if that’s good enough?

Reply | Quote

To do what you want to do, and still be able to do other things, you will need to use the Ontime method to schedule a time when you want to update the elapsed time display. Sorry, I don’t have any sample code showing how to do that.

Reply | Quote

I assume that all your times will work out positive ie start time is earlier than your start time. If you need to go negative try this function – its supposed to produce a string with minus figures.
Public Function TimeStr(Timedate1 As Date, Timedate2 As Date) As String
Dim TempStr As Double
Dim Hr As Integer
Dim Min As Integer
Dim sHr As String
Dim sMin As String
Dim i As Integer
Dim DayHrs As Integer
Dim TempHrs As Integer
Dim TempMins As Integer
Dim Time1 As Double
Dim Time2 As Double

i = 1
TempStr = Timedate1 – Timedate2
Hr = Hour(TempStr)
Min = Minute(TempStr)
If TempStr < 0 Then
i = -1
End If
Hr = (Hr * i) + DayHrs
If Min < 0 Then
Min = (Min * -1)
End If
If Min < 10 Then
sMin = "0" & Min
Else:
sMin = Min
End If

TimeStr = Hr & ":" & sMin

End Function

Reply | Quote

Great Suggestion Jim!
I don’t have a need for negatives as i’m dealing with call-times! )

For all those interested, Rory(M) has supplied the answer to this question in this thread.

Reply | Quote

Rory: Your method will not work if the elapsed time is more than 24 hours. My first thought was to use Format, but it apparently does not recognize the “[h]:mm:ss” as a valid format.

Reply | Quote

Legare,
Yeah, I noticed that about format, which is why I kind of trusted to luck that as it was a manual start/stop timer there wouldn’t be an issue with times over 24 hours! I should, however, have pointed that out so thanks for picking up my loose ends!

Reply | Quote

I tested the code in Excel 2000, but there isn’t anything in there that should not work in Excel 97. Which line are you getting the error on?

Reply | Quote

Given that we’re dealing with Call times, rory’s method is working like a charm! ) (I feel sorry for the poor sap who’s taking calls an excess of 24hrs…)

As for the error, It was on the first line of the code, VBA didn’t like the whole thing one bit..

Thanks Gentlemen!!

Drk

Reply | Quote