VBA time calculation

N

Nancy T.

I have created a command button. When I click it, I want it to get the
current time, add 16 hours to the current it, and enter that time in the
selected cell. I have spent way too much time trying to figure this out and I
am ready to pull my hair out. By the way, I am using Excel 2000. Any help
would be greatly appreciated!
 
M

muddan madhu

Private Sub CommandButton1_Click()
dt = Time
ActiveCell.Value = Format(dt + TimeValue("16:00:00"), "hh:mm:ss am/
pm")
End Sub
 
R

Rick Rothstein

This line of code should do what you want...

ActiveCell.Value = Time + TimeSerial(16, 0, 0)

You can, of course, format the cell to display this time in whatever format
you choose. Or you can set the NumberFormat property via code. For example
(to suppress the seconds using a 24-hour clock)...

ActiveCell.NumberFormat = "hh:mm"
 
R

Rick Rothstein

By the way, if you are only adding hours (even if decimal hours such as
14.25), then you can simplify this to the following...

ActiveCell = Time + 16/24

where you would replace the 16 by your decimal hours.
 
N

Nancy T.

Thanks everyone! I knew I was making it more difficult than it needed to be.
(My only excuse is that it was about 1:00 AM by the time I threw in the towel
and came here!) These are all perfect.

Gratefully,
--Nancy
 
Top