At some long ago time Steve Jorgensen answered thus:
Subject: Re: How can I round a time?
Newsgroups: comp.databases.msaccess
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 6 18056
Penguin wrote: <snipped intro and 1 minute case> 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.
Gregory Paret <gp****@cloud9.net> wrote in message news:<10*************@corp.supernews.com>... Penguin wrote: > <snipped intro and 1 minute case> 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....
Penguin wrote: At some long ago time Steve Jorgensen answered thus:
Subject: Re: How can I round a time? Newsgroups: comp.databases.msaccess 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
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.
Rereading 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 asis, so I think you
can remove the special case code if you wish to.
Greg.
Salad <oi*@vinegar.com> wrote in message news:<Lh4sd.403 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
I think you meant Function RT(vRoundTime As Date, intMinutes As Integer) As Variant
Dim intVal As Integer intVal = (24 * 60) / intMinutes
RT = CVDate(Int(vRoundTime * intVal + 0.5) / intVal)
It seems as though I should not have to test to do the DateAdd. I
thought I should be adding or subtracting half the number of minutes
(depending on whether user wants to round up or down) but
RT = CVDate(Int((vRoundTime * intVal) + intMinutes/2) / intVal)
doesn't seem to work. Adding 1 does. Why is that?
My final version  thanks Gregory & Salad:
Function RoundTimeNearestMinutes(vRoundTime As Date, intNearest As
Double, Optional blDirection As Boolean) As Variant
' Default is to round up
Dim intInterval As Integer
Dim dtTempTime As Date
intInterval = (24 * 60) / intNearest ' number of intervals of length
intNearest in a day
If Abs(Nz(blDirection, 1)) = 1 Then
' round it up
RoundTimeNearestMinutes = CVDate(Int((vRoundTime * intInterval +
1)) / intInterval)
Else
' round it down
RoundTimeNearestMinutes = CVDate(Int((vRoundTime * intInterval)) /
intInterval)
End If
End Function
Penguin wrote: Salad <oi*@vinegar.com> wrote in message news:<Lh4sd.403
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
I think you meant
No. Actually, I didn't even need intMinutes. I really don't care if
intVal changes when returned...maybe I should. Function RT(vRoundTime As Date, intMinutes As Integer) As Variant Dim intVal As Integer
intVal = (24 * 60) / intMinutes
RT = CVDate(Int(vRoundTime * intVal + 0.5) / intVal)
It seems as though I should not have to test to do the DateAdd. I thought I should be adding or subtracting half the number of minutes (depending on whether user wants to round up or down) but
RT = CVDate(Int((vRoundTime * intVal) + intMinutes/2) / intVal) doesn't seem to work. Adding 1 does. Why is that?
If you think of this logically, the above code I provided you already
rounded. So if you wanted it rounded up (or down) then that is
additional to the act of rounding the time. My final version  thanks Gregory & Salad:
Function RoundTimeNearestMinutes(vRoundTime As Date, intNearest As Double, Optional blDirection As Boolean) As Variant ' Default is to round up Dim intInterval As Integer Dim dtTempTime As Date intInterval = (24 * 60) / intNearest ' number of intervals of length intNearest in a day If Abs(Nz(blDirection, 1)) = 1 Then ' round it up RoundTimeNearestMinutes = CVDate(Int((vRoundTime * intInterval + 1)) / intInterval) Else ' round it down RoundTimeNearestMinutes = CVDate(Int((vRoundTime * intInterval)) / intInterval) End If End Function
Glad you got it to work to your satisfaction. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
9 posts
views
Thread by Penn Markham 
last post: by

3 posts
views
Thread by Ken 
last post: by

3 posts
views
Thread by teddysnips 
last post: by

3 posts
views
Thread by mg 
last post: by

4 posts
views
Thread by ey.markov 
last post: by

6 posts
views
Thread by Curley Q. 
last post: by

6 posts
views
Thread by karthi 
last post: by

4 posts
views
Thread by =?Utf8?B?UmVuZQ==?= 
last post: by

2 posts
views
Thread by dwasbig9 
last post: by
          