469,900 Members | 1,442 Online

Function to round time by a specified number of minutes

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
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 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.
Nov 13 '05 #2
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....
Nov 13 '05 #3
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
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.

"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
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
Nov 13 '05 #6
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

 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 =?Utf-8?B?UmVuZQ==?= | last post: by 2 posts views Thread by dwasbig9 | last post: by reply views Thread by eddparker01 | last post: by 1 post views Thread by isladogs | last post: by reply views Thread by Trystan | last post: by reply views Thread by Trystan | last post: by 6 posts views Thread by Kwabena10 | last post: by 1 post views Thread by Waqarahmed | last post: by 2 posts views Thread by Waqarahmed | last post: by reply views Thread by Salome Sato | last post: by reply views Thread by skydivetom | last post: by