How to Round a Duration

[NeoPa]

Hi my friend :-)

It's probably a lot easier than you may think. If you store your durations in Date/Time variables then everything just works. Look beyond the fact that one total would display as one particular Date & Time in history and when another duration is added it becomes equivalent to another Date & Time in history. That is only one interpretation of the data.

In Access 5 mins + 4 mins 30 secs + 6 mins 25 secs is equivalent to :

30/12/1899 00:05:00 +
30/12/1899 00:04:30 +
30/12/1899 00:06:25 =
30/12/1899 00:15:55

That seems a bit weird, obviously. However, that's because you're thinking of the value as a Date/Time instead of as a Duration.

Once you start thinking of it as a Duration you then start to see how it can work. The following makes much more sense. Bear in mind these are not Times in a day, and especially not Times of any particular Date. They're just Elapsed Time (Duration). 00:05:00 is not five past midnight. It's stored the same way, using the same value even, but instead it represents a Duration of 5 minutes.

00:05:00 +
00:04:30 +
00:06:25 =
00:15:55

Again, the result doesn't represent any specific Time of Day. We are not talking early hours of the morning at quarter past midight (~). Just an Elapsed Time of just over quarter of an hour in total.

As I said above (or below if you view posts in reverse order), when your values exceed a day then you need to be a bit creative with how they're displayed for a user, but don't let the display aspect confuse you. It does a fine job of maintaining the data for you.

Using the Format function in VBA (or SQL) you can show durations by displaying them twice like so :

Debug.Print Format(datDuration, "0"); Format(datDuration, " HH:nn:ss");

NB. As this is simple clarification of the original question and is useful to anyone looking for help on this subject there's no need for a separate question thread on it.

I hope I've answered your query adequately ADezii :-)

[ADezii]

You are right, it is a lot simpler than I thought, Thank You. For final clarification, if the User wanted the Random Sampling of Songs in the above Post to be < 18 minutes, the comparison would be?

Debug.Print #30/12/1899 00:15:55# <= #30/12/1899 00:18:00#

which would evaluate to

True

[Rabbit]

The real difficult part is something that hasn't been brought up yet. What margin of error is allowed here?

What happens if you need to fill 15:00, and you're currently at 10:30, and the next random song is 4:40. Do you just stay at 10:30? Do you go ahead and go over by 10 seconds? Do you try to find another song that will get it closer to 15:00 but not go over?

The full solution of trying every combination that get to exactly 15:00 is computationally expensive. You will most likely have to be satisfied with a partial solution in which you fill the first 10+ minutes with random songs and then grab the longest song that will fit for the last remaining amount.

[ADezii]

What margin of error is allowed here?

My original thought on this would have been the next logical step to Post# 13 which is a 90% Margin. Should the User enter a desired Total Duration of 30 minutes, continue the Random, Looping process as long as the Total Duration is < 27 minutes. Your thoughts on this approach?

[cactusdata]

This is the famous knapsack problem.
Radio stations cut it short by fading out the last song.

[ADezii]

@cactusdata:
Not my area of expertise, how critical are these predetermined Total Durations (why the need to fade out the last song)?

[cactusdata]

To allow for the next scheduled program to start on time (usually to the second), you will have to fade out any music playing.

[ADezii]

Thanks for the explanation.

[NeoPa]

Hi Rabbit.

You're right to say that hadn't been brought up yet. It wasn't, because there were more fundamental issues that needed clearing up first. That said, it is a good point to move on to. One that certainly helps deal with the original problem on a practical basis.

ADezii:
if the User wanted the Random Sampling of Songs in the above Post to be < 18 minutes, the comparison would be?

Debug.Print #30/12/1899 00:15:55# <= #30/12/1899 00:18:00#

Generally speaking, and in a way consistent with what we've already covered, durations would be used by specifiying only the time portion. Including the date part has the effect of causing people who read it to assume they're dealing with historical dates rather than durations. Thus, this could better be shown as :

Debug.Print #00:15:55# <= #00:18:00#

From Rabbit's extra comments you'd be looking to use a Between construct instead. So, assuming your current total is stored in datTotal and you're for a total ~= 30 mins then we may calculate the range required as anything between 28 & 30 minutes. Then you'd be looking at :

Debug.Print CBool(Eval(Format(datTotal,"\#HH:nn:ss\#") _
                     & " between #00:28# and #00:30#"));

The code's a little messy because it's VBA but I wanted to include the Between construct in case it's done in SQL.

[cactusdata]

I've never seen SQL and VBA mixed this way. Except for the fun, neither can I see a purpose as it becomes hard to grasp, indeed when compared to the simple code in each environment:

VBA:

datTotal >= #00:28# And datTotal <= #00:30#

SQL:

Where [Total] between #00:28# and #00:30#

[NeoPa]

I was working with limited information and wanted to illustrate a number of concepts quickly and succinctly.

I guess I didn't make a great job of it, but I wasn't posting that code as a suggestion - just to illustrate the various different possibilities.