How to Round a Duration

[ahayesfx]

I'm having an issue related to this old thread (how best to format time duration?). I made the same mistake as the original user, which I've corrected, but I'm stuck on the calculated portion of the solution. I want to know how to calculate for total time in specific increments, i.e. 30-minutes, 1-hour, etc. I've tried everything, but can't seem to make it happen.

{Admin Edit}
This post has been moved from the original thread (linked above) as posting your own questions in existing threads is hijacking - which is not allowed here (or in most forums).

[cactusdata]

It is relatively easy to round the total time, if that is what you are after:

Public Function RoundTime( _
    ByVal datDate As Date) _
    As Date
 
    Const cintMult As Integer = 48 '30 minute round
    'Const cintMult As Integer = 96 '15 minute round
    'Const cintMult As Integer = 144 '10 minute round
    'Const cintMult As Integer = 288 '5 minute round
 
    RoundTime = CDate(Int(datDate * cintMult + 0.5) / cintMult)
 
End Function
 
Public Function RoundTimeUp( _
    ByVal datDate As Date) _
    As Date
 
    Const cintMult As Integer = 48 '30 minute round
    'Const cintMult As Integer = 96 '15 minute round
    'Const cintMult As Integer = 144 '10 minute round
    'Const cintMult As Integer = 288 '5 minute round
 
    RoundTimeUp = CDate(-Int(-datDate * cintMult) / cintMult)
 
End Function

[ahayesfx]

Nevermind. I don't know how to word this to make sense. Sorry to have wasted anyone's time.

[NeoPa]

Hi ahayesfx.

First welcome to Bytes.com.

I had to move these posts as posting new questions in an existing thread (called hijacking) is not allowed here (or on most forum sites actually).

You come across as considerate and at least a little bright. Few would recognise how adequate a question is when it's their own question. Shame but true. Have you considered posting some sample data showing the data you start with as well as the results you hope to attain? Some people post sample data only and wonder why no-one understands what they want but if posted with a question that gets most of the idea across it can be invaluable. It's up to you of course but it could be that we're all but there for the want of a little push.

I'll leave it with you.

[ahayesfx]

I didn't mean to hijack the previous thread. I simply found a thread and solution that was similar to my question and made an amendment. It won't happen again.

Here's what I'm up against: I have a list of songs, varying in length, hundreds of them, and I want to build a series of queries that anytime one of them is ran, it will randomly select 15-minutes worth of songs, 30-minutes, 1-hour, 2-hour, 6-hour, etc. I have the randomizer already in-place, but I can't seem to configure the total times for each query. Instead of the Date/Time format, I used Short Text with an input mask of 00:00:00. My reason for doing this is because Access wants to automatically convert times into AM/PM, and I need it to track duration. In the query, I’ve set up three expressions: one for hours, one for minutes, and one for seconds. I thought doing so would help me in returning the totals. Unfortunately, this is where I get stuck.

EDIT: It's like when you want to return 'Top Values' in a query, except I want to return it by the total times listed above.

I hope all this makes sense. It doesn't seem like this should be as difficult as I'm making it, but yet here I am. Thank you for your assistance.

[ADezii]

Unless I am reading this incorrectly, you wish to select a Random Number of Songs, but keep the Total Duration of these Songs under some predetermined Value represented by Y? Should this premise be true, display some simple Data showing how each Song Title and Duration are stored. All other information (Artist, Label, etc.) is irrelevant for this discussion (IMHO).

[ahayesfx]

I think what you said in your response is what I'm looking to do. I hope these screenshots help.

https://drive.google.com/open?id=1-E...Jmx5GL2dbSqQPA

https://drive.google.com/open?id=1qq...zpQTpAN3ABaH-M

https://drive.google.com/open?id=10K...TID8l_mSE-sFM4

https://drive.google.com/open?id=1Wv...N-5Bof7THpdnOt

I just want to build a series of queries with a predetermined total: 15-minutes, 30-minutes, 1-hour, 2-hour, 6-hour, etc. For context purposes, I own and operate a radio station. I'm seeking to simplify the rotation and live show building processes. To accomplish this with an extremely limited staff, I built my own application. Thank you for all your help.

[ADezii]

Give me a little time, and I'll see what I can come up with.

[ahayesfx]

I greatly appreciate it.

[cactusdata]

Instead of the Date/Time format, I used Short Text with an input mask of 00:00:00. My reason for doing this is because Access wants to automatically convert times into AM/PM, and I need it to track duration.

This is definitely a dead end. Time(span) should always be handled using DateTime, not text, not numbers, no exceptions.

So, just sum the durations and format the result as you prefer. For spans exceeding 24 hours, however, a custom function like this must be used for display:

Public Function FormatHourMinuteSecond( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
 
' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
'
' 2014-06-17. Cactus Data ApS, CPH.
 
  Dim strHour       As String
  Dim strMinuteSec  As String
  Dim strHours      As String
 
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute and second count when needed.
  strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
  strHours = strHour & strSeparator & strMinuteSec
 
  FormatHourMinuteSecond = strHours
 
End Function

For an optimised and speedy entry of durations, please study my project VBA.TimeEntry