Function to round time by a specified number of minutes

[Penguin]

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

[Gregory Paret]

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.

[Penguin]

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....

[Salad]

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

[Gregory Paret]

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.

[Penguin]

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

[Salad]

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.