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

Here is one workaround using DateAdd

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

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison
Free & Pro versions


"Mark Stephens" wrote in message
...
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

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

--
Regards,

OssieMac


"Mark Stephens" wrote:

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

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



"OssieMac" wrote in message
...
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

--
Regards,

OssieMac


"Mark Stephens" wrote:

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

On Thu, 19 Feb 2009 14:14:34 +0800, "Mark Stephens" wrote:

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

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.

--
Regards,

OssieMac


"Mark Stephens" wrote:

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



"OssieMac" wrote in message
...
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

--
Regards,

OssieMac


"Mark Stephens" wrote:

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