446,182 Members | 1,141 Online Need help? Post your question and get tips & solutions from a community of 446,182 IT Pros & Developers. It's quick & easy.

# Function to round time by a specified number of minutes

 P: n/a At some long ago time Steve Jorgensen answered thus: Subject: Re: How can I round a time? Newsgroups: comp.databases.ms-access Date: 1998/12/11 Access represents a date internally as a double and will convert between date/time and double automatically. The double value Access (or VB) creates is based on 1 day = 1.0 and the fractional part represents a fraction of a day so a time interval of 3 hours = 3/24 = 0.125. To round a time, you simply multiply the fractional number times the number of rounding intervals per day, add 0.5, take the integer, and divide by the number of intervals per day. Example: Round to nearest 0:15. Intervals per day is 96 (24*4). Function RoundTime15m(vRoundTime As DateTime) As Variant RoundTime15m = CVDate(Int(vRoundTime*96.0+0.5)/96.0) End Function Here is my attempt at a variation thereof that would allow the user to specify what interval (1 minute, 5, 10, 15) and whether to round up or down. It works fine for 1 minute and for 15, but 5 & 10 just don't! Can anyone point out my undoubtedly obvious error? Function RoundTimeNearestMinutes(vRoundTime As Date, intNearest As Integer, Optional blDirection As Boolean) As Variant If intNearest = 1 Then ' you want round number of minutes If (Nz(blDirection, True)) = True Then ' round it up by adding the number of seconds, ' 60 - extra seconds, to make one minute RoundTimeNearestMinutes = DateAdd("s", 60 - DatePart("s", vRoundTime), vRoundTime) Else ' round it down by subtracting the seconds RoundTimeNearestMinutes = DateAdd("s", (-DatePart("s", vRoundTime)), vRoundTime) End If Else ' round to nearest 5, 10, 15 as passed If Nz(blDirection, True) = True Then ' round it up 'using 24*60=1440 min per day, '/ by minutes requested, + 0.5 RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) + 0.5) / (1440 / intNearest)) Else ' round it down RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) - 0.5) / (1440 / intNearest)) End If End If End Function Nov 13 '05 #1
6 Replies

 P: n/a Penguin wrote: If Nz(blDirection, True) = True Then ' round it up 'using 24*60=1440 min per day, '/ by minutes requested, + 0.5 RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) + 0.5) / (1440 / intNearest)) Else ' round it down RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) - 0.5) / (1440 / intNearest)) End If It seems okay to me for the round up case, but the round down case probably shouldn't be subtracting anything (the 0.5). ?CVDate(Int(timevalue("1:22:00") * (1440 / 5) - 0.5) / (1440 / 5)) 1:15:00 AM <--- oops ?CVDate(Int(timevalue("1:22:00") * (1440 / 5) - 0.0) / (1440 / 5)) 1:20:00 AM <--- better If you are still having problems, perhaps you could post some problem values? Also, why did you do the special case for 1 minute, as the generic case here should work for 1 minute too? -Greg. Nov 13 '05 #2

 P: n/a Gregory Paret wrote in message news:<10*************@corp.supernews.com>... Penguin wrote: > If Nz(blDirection, True) = True Then ' round it up 'using 24*60=1440 min per day, '/ by minutes requested, + 0.5 RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) + 0.5) / (1440 / intNearest)) Else ' round it down RoundTimeNearestMinutes = CVDate(Int(vRoundTime * (1440 / intNearest) - 0.5) / (1440 / intNearest)) End If It seems okay to me for the round up case, but the round down case probably shouldn't be subtracting anything (the 0.5). Yes, by golly, that works (at least on all the numbers I tested), wish I understood why.... If you are still having problems, perhaps you could post some problem values? ?roundtimenearestminutes(#4:11:45#,5,true) 4:10:00 AM - should be 4:15 if rounding up to nearest 5, no? ?roundtimenearestminutes(#4:11:45#,15,true) 4:15:00 AM ' OK ?roundtimenearestminutes(#4:11:45#,10,true) 4:10:00 AM ' should be 4:20 Also, why did you do the special case for 1 minute, as the generic case here should work for 1 minute too? I knew that for 1 minute I just have to subtract the seconds or add the seconds to make a minute....that, at least, works.. of course, I'd prefer to simplify the code if I can get one method to work for all. According to Excel, one minute as a fraction of a day is 0.0006944444. So how would you ever know that you chopped off the correct part? I knew how to write this function for numbers, but time seems a bit more mysterious .... as far as grasping the concept mentally, I mean, which of course has its practical ramifications as well. Meaning, I sort of managed to alter the code but without entirely understanding it.... Nov 13 '05 #3

 P: n/a Penguin wrote: At some long ago time Steve Jorgensen answered thus: Subject: Re: How can I round a time? Newsgroups: comp.databases.ms-access Date: 1998/12/11 Access represents a date internally as a double and will convert between date/time and double automatically. The double value Access (or VB) creates is based on 1 day = 1.0 and the fractional part represents a fraction of a day so a time interval of 3 hours = 3/24 = 0.125. To round a time, you simply multiply the fractional number times the number of rounding intervals per day, add 0.5, take the integer, and divide by the number of intervals per day. Example: Round to nearest 0:15. Intervals per day is 96 (24*4). Function RoundTime15m(vRoundTime As DateTime) As Variant RoundTime15m = CVDate(Int(vRoundTime*96.0+0.5)/96.0) End Function I simply modified Steve's function. I haven't tested it by passing a variable that has a remainder but...we know 24*60 is the number of minutes in a day. We divide that by the value of minutes you pass. Then use that. I expect a number that divides into 1440 with no remainder Now, if you want to add direction, you could get this time and see if the time calced is less than or greater than the time passed. If greater and you want down, DateAdd the interval with a negative number. If you want the larger number and the time calced is less than time passed, DateAdd the interval with a positive number. Function RT(vRoundTime As Date, intVal As Integer) As Variant Dim intMinutes As Integer intVal = (24 * 60) / intVal RT = CVDate(Int(vRoundTime * intVal + 0.5) / intVal) End Function Nov 13 '05 #4

 P: n/a Penguin wrote: Yes, by golly, that works (at least on all the numbers I tested), wish I understood why.... The Int() *does* the truncation to the lower value. You only need the "+.5" in the "round" case to fool the Int() by making "greater than half" values stride over into the next whole number value. ?roundtimenearestminutes(#4:11:45#,5,true) 4:10:00 AM - should be 4:15 if rounding up to nearest 5, no? The code you modeled yours on was doing "round up or down to nearest". 4:11:45 is less than 4:12:30 (which is half way between 4:10:00 and 4:15:00), so you get the lower value. Re-reading and seeing that your third parameter is "blDirection", and not "round", leads me to believe you want a "round up to next" action, instead of "round up/down". Try the following: CVDate(-Int(-CVDate(vRoundTime * (1440 / intNearest))) / (1440 / intNearest)) I knew that for 1 minute I just have to subtract the seconds or add the seconds to make a minute....that, at least, works.. of course, I'd prefer to simplify the code if I can get one method to work for all. Your general case code works for rounding to 1 minute as-is, so I think you can remove the special case code if you wish to. -Greg. Nov 13 '05 #5

 P: n/a Salad wrote in message news:

 P: n/a Penguin wrote: Salad wrote in message news:

### This discussion thread is closed

Replies have been disabled for this discussion. 