Elapsed times > 24hrs

[NDBC]

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[Jacob Skaria]

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes

 

[NDBC]

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[NDBC]

I'm guessing you've done the same but 960696/24 =40,029 days = 109.67 years
which just happens (i'm guessing) to be 109.67 years since 1/1/1900. Does
this mean it's doing elapsed time since the start of excel dates for some
reason.

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[Jacob Skaria]

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[NDBC]

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[NDBC]

Another update. I saved the start time in b6 using now and the format
"d/mm/yyyy hh:mm:ss". When i run the timer it now starts at 2832:00:00.

2832/24 = 118. Now I am very confused as I can't work out where this is
coming from. We are up to day 216 in the current year. I'm out of suggestions.

Thanks

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

:

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[Jacob Skaria]

You will have to save the date and time in B6 to substract from Now..If you
want to handle a blank entry in B6 use another variable

Sub RClock1()
Dim varTemp
varTemp = Sheets("Timing Sheet").Range("B6")
If varTemp = 0 then varTemp = Now
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
varTemp,"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub



If this post helps click Yes
---------------
Jacob Skaria

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

:

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[NDBC]

Jacob, I have saved the date and time in b6 but it is still giving me errors.

I was posting this when you replied

Another update. I saved the start time in b6 using now and the format
"d/mm/yyyy hh:mm:ss". When i run the timer it now starts at 2832:00:00.

2832/24 = 118. Now I am very confused as I can't work out where this is
coming from. We are up to day 216 in the current year. I'm out of suggestions.

Thanks

You will have to save the date and time in B6 to substract from Now..If you
want to handle a blank entry in B6 use another variable

Sub RClock1()
Dim varTemp
varTemp = Sheets("Timing Sheet").Range("B6")
If varTemp = 0 then varTemp = Now
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
varTemp,"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub



If this post helps click Yes
---------------
Jacob Skaria

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria


:

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

:

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[Jacob Skaria]

--How do you enter the date/time in B6. Through code or how?
--The date/time entered in B6 should be excel date/time format? Is that so?
--Try this test
In B4 enter date (Ctrl+ semicolon)
In B5 enter time (Ctrl + Shift + semicolon)
In B6 enter formula =B4+B5

Now try the code

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I have saved the date and time in b6 but it is still giving me errors.

I was posting this when you replied

Another update. I saved the start time in b6 using now and the format
"d/mm/yyyy hh:mm:ss". When i run the timer it now starts at 2832:00:00.

2832/24 = 118. Now I am very confused as I can't work out where this is
coming from. We are up to day 216 in the current year. I'm out of suggestions.

Thanks

You will have to save the date and time in B6 to substract from Now..If you
want to handle a blank entry in B6 use another variable

Sub RClock1()
Dim varTemp
varTemp = Sheets("Timing Sheet").Range("B6")
If varTemp = 0 then varTemp = Now
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
varTemp,"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub



If this post helps click Yes
---------------
Jacob Skaria

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

:

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria


:

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

:

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

 

[OssieMac]

How about a different approach. Perform all calculations on a worksheet and
copy result to the Userform controls.

Advantages:
Can obtain results down to tents of second on the worksheet because NOW()
function returns tenths of second on worksheet.
Can format result to [hh]:mm:ss.00 .
Worksheet automatically handles subtracting the times because dates are
included even if not displayed.

Example: (Assume all on worksheet "Timing Sheet" but could be placed anywhere.
In Cell B6 Enter formula = NOW()
Format cells C5:C6 to "dd mm yyyy hh:mm:ss.00" (or any date format you like).
Format cell C7 to "[hh]:mm:ss.00" (Note displays tenths second)
In cell C7 enter formula =C5-C6


On userform TextBox1 as Start time (Can be eliminated later. I just used it
for testing)
On userform CommandButton1 starts the timer.
On userform CommandButton2 stops the timer.
On userform RaceClock1 contains elapsed time.

Note in the code below that the elapsed time only rolls over in 1 second
intervals but the stop button ensures final elapsed time gets the final
tenths of a second.

For testing only to see what occurs with greater than 24 hours, set the
formula in cell B5 to =NOW()-1

Following code in Userform module:
Option Explicit

Private Sub CommandButton1_Click()
'Start timer

MyStop = False

Calculate 'force update of NOW()

'copy current NOW() time from B6 to C6
With Sheets("Timing Sheet")
.Range("C6") = .Range("B6")
End With

'Display Start time on userform textbox.
'(Could format including date if required).
Me.TextBox1 = WorksheetFunction _
.Text(ActiveSheet.Range("B6"), _
"hh:mm:ss.00")

Call RClock1

End Sub

Private Sub CommandButton2_Click()
'Stop timer

'do not wait for RClock1 to update elapsed time
'or will loose accuracy for tenths of second.
Calculate
With Sheets("Timing Sheet")
UserForm1.RaceClock1.Text = _
WorksheetFunction.Text _
(.Range("C7"), "[hh]:mm:ss.00")
End With

MyStop = True
End Sub

Copy following code into a module.

Option Explicit
'Note following variable declared in
'Declarations area before any subs
Public MyStop As Boolean

Sub RClock1()
Dim NextTick As Date

If MyStop = True Then Exit Sub
Calculate 'force update of NOW()

With Sheets("Timing Sheet")
UserForm1.RaceClock1.Text = _
WorksheetFunction.Text _
(.Range("C7"), "[hh]:mm:ss.00")
End With

NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"

End Sub

Sub test()
UserForm1.Show
End Sub

 

[OssieMac]

That should have been accuracy to 100th sec but you cannot rely on the end
result better than tenth sec due to time taked to run code etc.

 

[NDBC]

Thanks Jacob. I was a bit slow getting back to it due to work committments
but it is fine now. I was/am using code.

I had b6 = format(now, "hh:mm:ss")

changed it to b6 = now and all is fine. Then just format the cell to only
show the time not the date as well.

There is a lot to be said for good planning before beginning programming. A
lesson learnt the hard way. The only good news is it is re-enforcing some of
the new things I have learnt.

--How do you enter the date/time in B6. Through code or how?
--The date/time entered in B6 should be excel date/time format? Is that so?
--Try this test
In B4 enter date (Ctrl+ semicolon)
In B5 enter time (Ctrl + Shift + semicolon)
In B6 enter formula =B4+B5

Now try the code

If this post helps click Yes
---------------
Jacob Skaria

Jacob, I have saved the date and time in b6 but it is still giving me errors.

I was posting this when you replied

Another update. I saved the start time in b6 using now and the format
"d/mm/yyyy hh:mm:ss". When i run the timer it now starts at 2832:00:00.

2832/24 = 118. Now I am very confused as I can't work out where this is
coming from. We are up to day 216 in the current year. I'm out of suggestions.

Thanks

You will have to save the date and time in B6 to substract from Now..If you
want to handle a blank entry in B6 use another variable

Sub RClock1()
Dim varTemp
varTemp = Sheets("Timing Sheet").Range("B6")
If varTemp = 0 then varTemp = Now
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
varTemp,"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub



If this post helps click Yes
---------------
Jacob Skaria


:

Sorry Jacob, I didn't make myself clear. I never run the form without having
a time in cell b6. I had just reset the time in b6 so when i started the form
i was expecting a time of say 00:00:10 or less but got 960696:00:10. i save
the time in cell b6 using the format "hh:mm:ss". Maybe I need to save the
date as well. Could that be it.

Thanks.

:

May be we will have to handle the 0 value.

If Sheets("Timing Sheet").Range("B6").Value > 0 Then
UserForm1.RaceClock1.Text = WorksheetFunction.Text(Now - _
Sheets("Timing Sheet").Range("B6"), "[h]:mm:ss")
End If

If this post helps click Yes
---------------
Jacob Skaria


:

Jacob, I think it is almost there. the minutes and seconds now start at zero
but the hours (are shown) start at 960696. So at time zero the timer shows
960696:00:00.
Thanks for you efforts.

:

Try

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Worksheetfunction.text(Now - _
Sheets("Timing Sheet").Range("B6"),"[h]:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

I have the following code for a timer on a form. the time in cell b6 is the
start time of the race. I want it to work for races that go for > than 24
hours. I have tried formatting it as "[hh]:mm:ss" but the hours disappear and
the timer starts at 8 minutes for some reason regardless of what the start
time is. Any ideas.

Thanks

Sub RClock1()

Watch = Now - Sheets("Timing Sheet").Range("B6")
UserForm1.RaceClock1.Text = Format(Watch, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "RClock1"
End Sub