Timer macro

[sunilpatel]

I have data as follows

A1=00:00:30 (representing a counter 00 hours 00 minutes 37 seconds)

B1=00:00:45 (representing a counter 00 hours 00 minutes 42 seconds)

C1=00:01:00

The cells are formatted as hh:mm:ss

i need a macro that will add a certain time interval (positive or negative)
to A1 (via inputbox) e.g +31 seconds
and each subsequent time interval increases or decreases by this amount.
If 31 seconds is inputed

A1 will read "00:01:01"
B1 will read "00:01:16" and
C1 will read "00:01:31"

is there a simple macro that can do this, or will i have to write a long
macro looking at each seperat component i.e hh then mm then ss and using
lots of if s%>60, and if m%>60 and if h%>60 etc etc.....


Sunil

 

[CFS]

Sunil,

You can use TimeSerial function:

Sheets(1).Range("A1") = Sheets(1).Range("A1") + TimeSerial(0, 0, 31)

Function Syntax: TimeSerial(hour, minute, second)

CFS

 

[Bob Phillips]

Dim addon As String

addon = InputBox("Supply time addon in format hh:mm:ss")
Range("A1").Value = Range("A1").Value + TimeValue(addon)
Range("B1").Value = Range("B1").Value + TimeValue(addon)
Range("C1").Value = Range("C1").Value + TimeValue(addon)

 

[Leith Ross]

I have data as follows

A1=00:00:30 (representing a counter 00 hours 00 minutes 37 seconds)

B1=00:00:45 (representing a counter 00 hours 00 minutes 42 seconds)

C1=00:01:00

The cells are formatted as hh:mm:ss

i need a macro that will add a certain time interval (positive o
negative)
to A1 (via inputbox) e.g +31 seconds
and each subsequent time interval increases or decreases by thi
amount.
If 31 seconds is inputed

A1 will read "00:01:01"
B1 will read "00:01:16" and
C1 will read "00:01:31"

is there a simple macro that can do this, or will i have to write
long
macro looking at each seperat component i.e hh then mm then ss an
using
lots of if s%>60, and if m%>60 and if h%>60 etc etc.....


Sunil

Hello Sunil,

You can use the VBA *TimeSerial* function. This is from the VBA hel
file...

a Variant (Date) containing the time for a specific hour, minute, an
second.

Syntax

TimeSerial(hour, minute, second)

The TimeSerial function syntax has these named arguments:

Part Description
hour Required; Variant (Integer). Number between 0 (12:00 A.M.) and 2
(11:00 P.M.), inclusive, or a numeric expression.
minute Required; Variant (Integer). Any numeric expression.
second Required; Variant (Integer). Any numeric expression.



Remarks

To specify a time, such as 11:59:59, the range of numbers for eac
TimeSerial argument should be in the normal range for the unit; that is
0–23 for hours and 0–59 for minutes and seconds. However, you can als
specify relative times for each argument using any numeric expressio
that represents some number of hours, minutes, or seconds before o
after a certain time. The following example uses expressions instead o
absolute time numbers. The TimeSerial function returns a time for 1
minutes before (-15) six hours before noon (12 - 6), or 5:45:00 A.M.

TimeSerial(12 - 6, -15, 0)

When any argument exceeds the normal range for that argument, i
increments to the next larger unit as appropriate. For example, if yo
specify 75 minutes, it is evaluated as one hour and 15 minutes. If an
single argument is outside the range -32,768 to 32,767, an error occurs
If the time specified by the three arguments causes the date to fal
outside the acceptable range of dates, an error occurs.

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/