Working with time variables

[brenaud]

Mod. edit: CODE tags at all times please (per Forum Rules) - modified on your behalf on this occasion

Evening folks,

I'm working with time variables for the first time, and encountering some challenges. Here's what I'm trying to do :

Cell A14 = Start time of a movie as a time stamp
Cell A21 = End time of a movie as a time stamp

I want to get the duration of the movie and then in a column on a second sheet, create a column of durations in 5 minute increments which does not exceed the duration of the movie. For example, if the movie is 2 hours long it will create a column like this :

00:05:00
00:10:00
00:15:00
...
02:00:00

Here's how I've tried doing it. It creates the column of data, but it never meets my exit condition for the loop and I end up with an overflow for the 'i' integer and a column thousands of rows long full of time stamps.

Please Login or Register  to view this content.

If I could convert everything to an easier variable time such as an integer for just minutes, that would be convenient and I could make this work a lot easier. For example, convert MDuration from 02:00:00 to 120.

Thanks folks !

[DonkeyOte]

In terms of your VBA - unless your duration is always an exact multiple of 5 minutes MDuration won't equal 0.
In terms of modifying existing code you should perhaps use > 0 rather than <> 0

FWIW, you could achieve similar using native functions.

[brenaud]

In terms of your VBA - unless your duration is always an exact multiple of 5 minutes MDuration won't equal 0.
In terms of modifying existing code you should perhaps use > 0 rather than <> 0

FWIW, you could achieve similar using native functions.

You are correct, I modified the code when I posted it to make it relevant to what I was actually trying to achieve. While I've been playing with this, I was using a duration of 5-10 seconds and using increments of 1 second...so I assumed that since that was the smallest value in the variable, it would eventually hit 0.

Which native function can I use to accomplish this ?

My apologies, I'm picking up VBA after 5 years of neglect and to say I'm a bit rusty would be generous.

[brenaud]

Resolved, sort of.

As a work around, I converted my time values to integers which made them nice and easy to work with.