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

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


"sunilpatel" wrote:

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

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)


--
__________________________________
HTH

Bob

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

sunilpatel;192730 Wrote:
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

Hello Sunil,

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


a Variant (Date) containing the time for a specific hour, minute, and
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 23
(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 each
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 also
specify relative times for each argument using any numeric expression
that represents some number of hours, minutes, or seconds before or
after a certain time. The following example uses expressions instead of
absolute time numbers. The TimeSerial function returns a time for 15
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, it
increments to the next larger unit as appropriate. For example, if you
specify 75 minutes, it is evaluated as one hour and 15 minutes. If any
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 fall
outside the acceptable range of dates, an error occurs.



--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=53131