By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

How to Round a Duration

P: 6
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).
2 Days Ago #1
Share this Question
Share on Google+
14 Replies

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

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundTime( _
  2.     ByVal datDate As Date) _
  3.     As Date
  5.     Const cintMult As Integer = 48 '30 minute round
  6.     'Const cintMult As Integer = 96 '15 minute round
  7.     'Const cintMult As Integer = 144 '10 minute round
  8.     'Const cintMult As Integer = 288 '5 minute round
  10.     RoundTime = CDate(Int(datDate * cintMult + 0.5) / cintMult)
  12. End Function
  14. Public Function RoundTimeUp( _
  15.     ByVal datDate As Date) _
  16.     As Date
  18.     Const cintMult As Integer = 48 '30 minute round
  19.     'Const cintMult As Integer = 96 '15 minute round
  20.     'Const cintMult As Integer = 144 '10 minute round
  21.     'Const cintMult As Integer = 288 '5 minute round
  23.     RoundTimeUp = CDate(-Int(-datDate * cintMult) / cintMult)
  25. End Function
2 Days Ago #2

P: 6
Nevermind. I don't know how to word this to make sense. Sorry to have wasted anyone's time.
2 Days Ago #3

Expert Mod 15k+
P: 31,487
Hi ahayesfx.

First welcome to

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.
1 Days ago #4

P: 6
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.
1 Days ago #5

Expert 5K+
P: 8,634
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).
1 Days ago #6

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

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.
1 Days ago #7

Expert 5K+
P: 8,634
Give me a little time, and I'll see what I can come up with.
1 Days ago #8

P: 6
I greatly appreciate it.
1 Days ago #9

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function FormatHourMinuteSecond( _
  2.   ByVal datTime As Date, _
  3.   Optional ByVal strSeparator As String = ":") _
  4.   As String
  6. ' Returns count of days, hours, minutes, and seconds of datTime
  7. ' converted to hours, minutes, and seconds as a formatted string
  8. ' with an optional choice of time separator.
  9. '
  10. ' Example:
  11. '   datTime: #10:03:55# + #20:01:24#
  12. '   returns: 30:05:19
  13. '
  14. ' 2014-06-17. Cactus Data ApS, CPH.
  16.   Dim strHour       As String
  17.   Dim strMinuteSec  As String
  18.   Dim strHours      As String
  20.   strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  21.   ' Add leading zero to minute and second count when needed.
  22.   strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
  23.   strHours = strHour & strSeparator & strMinuteSec
  25.   FormatHourMinuteSecond = strHours
  27. End Function
For an optimised and speedy entry of durations, please study my project VBA.TimeEntry
23 Hours Ago #10

Expert 5K+
P: 8,634
I see the difficult part being obtaining a Random Sampling of Songs whose Total Duration is <= a User entered Value. I kept the Length Field as Formatted Text (hh:nn:ss) sine I found it easier to work with and aggregate. I created a simple Database that prompts the User for a Total Duration in hours ranging from .25 (15 minutes) to 6 (6 hours). Should the Value entered exceed the Total Duration of all Songs in the DB, all Songs would be returned. This is very simple Demo, so only enter value of .25 (15 mins.) or .5 (30 mins.) when prompted. The Sampling should be Random and will always be <= the Duration. I will not ramble any longer, so I'll just upload the Sample DB.
Attached Files
File Type: zip Random (30.3 KB, 3 views)
23 Hours Ago #11

P: 6
Thank you! Although I consider myself to have a strong handle on VBA, I would also still claim to be a novice. Technically, this project is my trial by fire for VBA. That said, would you be so kind as to walk me through how I'm to go about adding this code to my project? I appreciate it.

While in the process of replying to @cactusdata, I received a response from @ADezii. I will take a look at what you sent, much appreciated. Like I said above, I am learning a lot about VBA through this project. I originally tried to proceed in this direction, but due to my limitations I hit a wall. I can't thank you enough for all your help.
23 Hours Ago #12

Expert 5K+
P: 8,634
  1. The first thing that you would need to do is to duplicate qryDuration. This Query is based on tblTracks and has two distinct functions:
    1. It calculates the Duration in minutes as SINGLE Data Type. It contains a Calculated Field named [Duration] and this Field passes the value in the [Length] Field to the Public Function fCalcDurationInMinutes() as follows:
      Expand|Select|Wrap|Line Numbers
      1. Duration: fCalcDurationInMinutes([Length])
      • The [Length] Field must be TEXT, formatted as hh:nn:ss, and have an Input Mask of 00:00:00.
      • The [Length] Field cannot be NULL (Required must be set to True).
    2. qryDuration also generates a Random Sort Order by using the Rnd() Function and seeding it with the Primary Key [SID].
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcDurationInMinutes(strLength As String) As Single
    2.   fCalcDurationInMinutes = CInt(Left$(strLength, 2)) * 60 + CInt(Mid$(strLength, 3, 2)) + Val(Right$(strLength, 2)) / 60
    3. End Function
  3. Create a Query named qryRandomSongs which simply returns all Records from tblTracks. The SQL of this Query will later be modified based on the Value entered by the User when prompted.
  4. The SelectRandomSongs() Sub-Routine does all the work. I won't bore you with details since I have already Uploaded it.
    Expand|Select|Wrap|Line Numbers
    1. Public Sub SelectRandomSongs()
    2. Dim strResponse As String
    3. Dim sngTotDurInHrs As Single
    4. Dim sngDurationBuild As Single
    5. Dim sngTotalRequestedDuration As Single
    6. Dim rst As DAO.Recordset
    7. Dim strBuild As String
    8. Dim qdf As DAO.QueryDef
    10. strResponse = InputBox$("ENTER Total Duration in Hours" & vbCrLf & "[.25-(15 mins.) to 6-(6 hrs.)]", "Total Duration")
    12. 'Not a Number or Empty String
    13. If Not IsNumeric(strResponse) Or strResponse = "" Then Exit Sub
    15. 'At least we have a Number, convert it to a single
    16. sngTotDurInHrs = CSng(strResponse)
    18. 'Make sure the Total Duration is between 15 minutes (.25) and 6 Hours (6)
    19. If sngTotDurInHrs < 0.25 Or sngTotDurInHrs > 6 Then
    20.   MsgBox "The Total Duration must be between" & vbCrLf & "15 minutes (.25) and 6 Hours (6)!", _
    21.           vbExclamation, "Invalid Entry"
    22.     Exit Sub
    23. End If
    25. sngTotalRequestedDuration = sngTotDurInHrs * 60
    27. Set rst = CurrentDb.OpenRecordset("qryDuration", dbOpenSnapshot)
    29. With rst
    30.   Do While sngDurationBuild <= sngTotalRequestedDuration And Not .EOF
    31.     sngDurationBuild = sngDurationBuild + ![Duration]
    32.       If sngDurationBuild <= sngTotalRequestedDuration Then
    33.         strBuild = strBuild & ![SID] & ", "
    34.       End If
    35.       .MoveNext
    36.   Loop
    37. End With
    39. Set qdf = CurrentDb.QueryDefs("qryRandomSongs")
    40.     qdf.SQL = "Select * FROM tblTracks WHERE [SID] In(" & Left$(strBuild, Len(strBuild) - 2) & ")"
    42. DoCmd.OpenQuery "qryRandomSongs", acViewNormal, acReadOnly
    44. rst.Close
    45. Set rst = Nothing
    46. End Sub
  5. The SelectRandomSongs() Routine can be called as follows:
    Expand|Select|Wrap|Line Numbers
    1. Call SelectRandomSongs
20 Hours Ago #13

Expert Mod 15k+
P: 31,487
I have a message for both the OP (Original Poster = ahayesfx for this thread) and ADezii (My very old friend who's been on this site even longer than I have).

The message is that cactusdata is absolutely on the button when he says you should never try to manage durations in anything other than Date/Time variables and fields.

I sort of know how you think now, after all these years. You always get there, and invariably help others along the way while you're about it, but sometimes you take the long road round the mountain instead of the train through the tunnel. This is no criticism. You've done brilliantly without ever having any training.

Cactusdata may struggle to express why in English (Not his native language.) so let me see if I can fill in some of the gaps.

Firstly, don't worry about how Date/Times are formatted when displayed. You can control that anyway, but it isn't any part of the consideration of how to process the data. If it helps to know, it shows on your PC as AM/PM simply because your default Date/Time format settings are set that way. Mine show as 24 hour clock because that's how I have it set. The take-away from this is that it's configurable as well as 100% irrelevant.

Date/Time fields are specifically designed to handle dates & times in a natural way. The only thing about them is that they don't naturally display as durations for periods greater than a day. I expect that isn't a worry here for you though. I know some tracks can be lengthy but even they wouldn't stretch to >day. Anything less can be done using the various options of the Format() function which is available both in VBA and in Jet or ACE SQL.

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.
That's fine. I have to inform people but so many come to forums without previous experience that we expect some stumbles at the start.

By the look of things already you'll be perfectly fine and manage to get a great deal of help and guidance from our experts here.

Which reminds me. Cactusdata is actually another Access MVP so I should probably get around to short-circuiting the process of getting him upgraded to Expert at least. It does mean you can rely on any advice he gives.
13 Hours Ago #14

Expert 5K+
P: 8,634
First and foremost, it is always a pleasure to hear from an old (not in a literal sense) friend. I hope that you are doing well and from what I see you are still as impressive as always. I fully understand what cactusdata and you are saying and I actually agree with you. What I cannot fathom is how, given the fact that the Duration is stored in a Date/Time Field, you can maintain a Running Sum of each Random Song's Duration and check it against the Total Duration as inputted by the User? I'm sure it's something simple that I am missing. Thanks in advance.
6 Hours Ago #15

Post your reply

Sign in to post your reply or Sign up for a free account.