VBA time calculation

  • Thread starter Thread starter Nancy T.
  • Start date Start date
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!
 
Private Sub CommandButton1_Click()
dt = Time
ActiveCell.Value = Format(dt + TimeValue("16:00:00"), "hh:mm:ss am/
pm")
End Sub
 
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"
 
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.
 
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
 
Back
Top