Adding a value to a Time in Excel VBA

Hi,

I have a bunch of times stored as hh:mm, and I want to be able to add a set period (i.e. an hour or a minute) to a time within some VBA. I have made several attempts along the lines of:
ActiveCell.Value = ActiveCell.Value + "00:01"
...with the intention of adding a minute to the current cell. Debugging reveals that the value of the active cell is processed as a number <1, as expected, but there is type mismatch when the "00:01" is added. If the original cell is blank (i.e. 0), the assignment works, and returns 00:01.

Pretty sure this should be very straightforward, and I'm missing something obvious - can anyone point me in the right direction please?

Ian

Here are a couple of options:

A day is 1.
So an hour is 1/24 and a minute is 1/24/60


'add one minute
ActiveCell.Value = ActiveCell.Value + (1 / 24 / 60)


You can also do it using the VBA.DateAdd() function, for example:

'add one minute
ActiveCell.Value = VBA.DateAdd("n", 1, ActiveCell.Value)

Hope that helps...

Here are a couple of options:

A day is 1.
So an hour is 1/24 and a minute is 1/24/60


'add one minute
ActiveCell.Value = ActiveCell.Value + (1 / 24 / 60)


You can also do it using the VBA.DateAdd() function, for example:

'add one minute
ActiveCell.Value = VBA.DateAdd("n", 1, ActiveCell.Value)

Hope that helps...

Hi Colin, thanks for the advice - here are my results:

Running the above options on a cell containing 17:00 formatted [hh]:mm I get:

01/01/1900 02:04:39, displayed as 26:04
and for the second option
01/01/1900 02:05:36, displayed as 26:05

Neither are the 17:01 I was hoping for, and I can't see any logical pattern there - it seems to have added 9 hours and 4 or 5 minutes.

I've just noticed it appears to be adding on the current time (which was just after 9am when i tried it), not the amount of time I specified.

Errr... Sorry about that, my mistake. It does in fact work perfectly - I had left some earlier code in further down that was causing the problem!

Thanks a lot for your help!