Adding Time (2002)

Topic

New Reply

I am trying to add exactly 1 hour to a cell formated as custom, h:mm AM/PM into another cell with the same format. I use this code:

Target.Offset(0, 1) = Target.Value + 0.04167

I had to experiment to find that .04167 seems to give me the exact hour. I

Reply | Quote

Replies

There are several possibilities, each of which can easily be modified for other time values:

Target.Offset(0,1) = DateAdd(“h”, 1, Target.Value)

Target.Offset(0,1) = Target.Value + TimeValue(“1:00”)

Target.Offset(0,1) = Target.Value + TimeSerial(1, 0, 0)

You can get help for the DateAdd, TimeValue and TimeSerial functions by typing them in a VBE window and pressing F1.

Reply | Quote

Thank you Hans and for the quick response! I thought there was a better way than trying to experiment like I did. I’ll read up on those three functions and get familiar with them.

Reply | Quote

There are several possibilities, each of which can easily be modified for other time values:

Target.Offset(0,1) = DateAdd(“h”, 1, Target.Value)

Target.Offset(0,1) = Target.Value + TimeValue(“1:00”)

Target.Offset(0,1) = Target.Value + TimeSerial(1, 0, 0)

You can get help for the DateAdd, TimeValue and TimeSerial functions by typing them in a VBE window and pressing F1.

Reply | Quote

Just to add to what Hans said. .041667 is 1 hour divided by 24 hours (the number of hours in a day). Excel keeps time as the fractional part of a day. The equivalent for 30 minutes would be 30/1440 where 1440 is the number of minutes in a day.

Reply | Quote

Thanks for the explanation Legare. Now I know why my number worked. It’s good to know there are three other ways that are better than experiementing.

Reply | Quote

Thanks for the explanation Legare. Now I know why my number worked. It’s good to know there are three other ways that are better than experiementing.

Reply | Quote