Time bug, anyone know why?

[Mark Stephens]

Hi all,

I am trying to increment a time for a shceduling feature in my program. I
want to increase the time by 1 hour each time i click a button. Here's the
methodology:

1. Read the cell containing the system date and time

2. Add to the number (1/24)

3. Update the button caption


It's pretty simple really but what happens is that instead of increasing the
time by an hour as intended it increases it by 1 hour and one minute!
Frustrating and annoying. Adjusting the increment slightly doesn't seem to
have much effect at all. I have done some searching on google in this
newsgroup and whilst i have found some workarounds such as using ceiling or
floor or rounding, noone hseems to have addressed the basic issue which is
why the time concept of 1/24 for an hour doesn't work; I suppose it could be
somethibng to do with rounding (is vba rounding the decimal up to the next
one minute incrememtn automatically?) seems strange any
suggestions/explanations/workarounds gratwefully received.

Thanks and regards, Mark

 

[Tim Zych]

Here is one workaround using DateAdd

Dim dt As Date
dt = #1/1/2008 12:55:00 PM#
MsgBox DateAdd("h", 1, dt)

 

[OssieMac]

Hi mark,

It is always advisable to post the actual code you have. However, the
following code appears to work OK.

Note that space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sub testAddTime()

Dim timeNow As Date
Dim timeNext As Date

timeNow = Now()

'Both the following lines appear to work under test
'dateNext = dateNow + TimeValue("01:00:00")
timeNext = timeNow + 1 / 24

MsgBox Format(timeNow, "hh:mm:ss") & vbCrLf & _
Format(timeNext, "hh:mm:ss")

'Code for caption on ActiveX button
'(Button Created from Control Toolbox Toolbar)
ActiveSheet.CommandButton1.Caption = _
Format(timeNext, "hh:mm:ss")

'Code for a button created from Forms Toolbar
'Not sure if is best code practice.
ActiveSheet.Shapes("Button 3").Select
Selection.Characters.Text = Format(timeNext, "hh:mm:ss")
End Sub

 

[Mark Stephens]

Hi, thanks for that, I trie d the first version and it has the same
phenomena... it increments by 1 hour and one minute rather than just one
hour (which is very strange).

I actually had the button caption display Now, and the message box now +
TimeValue("01:00:00")

The timne now (the button) displayed 16:16 and the message box 17:17 which
is pretty bizarre although i am sure there's an explanation for it
somewhere. i even modified it to make the timeNext = timeNext - 1 minute
(1/24/60) but still made no difference!

Regards, Mark

 

[Ron Rosenfeld]

Hi all,

I am trying to increment a time for a shceduling feature in my program. I
want to increase the time by 1 hour each time i click a button. Here's the
methodology:

1. Read the cell containing the system date and time

2. Add to the number (1/24)

3. Update the button caption


It's pretty simple really but what happens is that instead of increasing the
time by an hour as intended it increases it by 1 hour and one minute!
Frustrating and annoying. Adjusting the increment slightly doesn't seem to
have much effect at all. I have done some searching on google in this
newsgroup and whilst i have found some workarounds such as using ceiling or
floor or rounding, noone hseems to have addressed the basic issue which is
why the time concept of 1/24 for an hour doesn't work; I suppose it could be
somethibng to do with rounding (is vba rounding the decimal up to the next
one minute incrememtn automatically?) seems strange any
suggestions/explanations/workarounds gratwefully received.

Thanks and regards, Mark

Please post the exact code you are using. I cannot replicate your problem
here.
--ron

 

[OssieMac]

Hi again Mark,

The example as I gave it to you works perfectly for me.

Your quote: "I actually had the button caption display Now, and the message
box now + TimeValue("01:00:00")"

You cannot display the value of now in one line and then in another line add
something to Now and expect that it will always return a value exactly
incremented by the value added.

There is a time delay between the two even though it might be small; the
delay is there. The actual time of Now could be in the phase of moving from
one minute to the next.

You need to assign Now to a variable the way I did it so that it is fixed
and then add something to the variable if you want it to return a value
exactly incremented by the added time.

Anyway, please post the exact code you have as per Ron's suggestion and
maybe can test a little more.