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(vR oundTime As DateTime) As Variant
RoundTime15m = CVDate(Int(vRou ndTime*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 RoundTimeNeares tMinutes(vRound Time 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
RoundTimeNeares tMinutes = DateAdd("s", 60 - DatePart("s",
vRoundTime), vRoundTime)
Else
' round it down by subtracting the seconds
RoundTimeNeares tMinutes = 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
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (1440 /
intNearest) + 0.5) / (1440 / intNearest))
Else
' round it down
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (1440 /
intNearest) - 0.5) / (1440 / intNearest))
End If
End If
End Function
6  18655 
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
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (1440 /
intNearest) + 0.5) / (1440 / intNearest))
Else
' round it down
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (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(tim evalue("1:22:00 ") * (1440 / 5) - 0.5) / (1440 / 5))
1:15:00 AM <--- oops
?CVDate(Int(tim evalue("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.sup ernews.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
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (1440 /
intNearest) + 0.5) / (1440 / intNearest))
Else
' round it down
RoundTimeNeares tMinutes = CVDate(Int(vRou ndTime * (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?
?roundtimeneare stminutes(#4:11 :45#,5,true)
4:10:00 AM - should be 4:15 if rounding up to nearest 5, no?
?roundtimeneare stminutes(#4:11 :45#,15,true)
4:15:00 AM ' OK
?roundtimeneare stminutes(#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. 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(vR oundTime As DateTime) As Variant
RoundTime15m = CVDate(Int(vRou ndTime*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(vRou ndTime * 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.
?roundtimeneare stminutes(#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 "blDirectio n", 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(vRoundTi me * (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.
Salad <oi*@vinegar.co m> 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(vRou ndTime * 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(vRou ndTime * 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((vRo undTime * intVal) + intMinutes/2) / intVal)
doesn't seem to work. Adding 1 does. Why is that?
My final version - thanks Gregory & Salad:
Function RoundTimeNeares tMinutes(vRound Time 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(blDirect ion, 1)) = 1 Then
' round it up
RoundTimeNeares tMinutes = CVDate(Int((vRo undTime * intInterval +
1)) / intInterval)
Else
' round it down
RoundTimeNeares tMinutes = CVDate(-Int((vRoundTime * intInterval)) /
intInterval)
End If
End Function
Penguin wrote: Salad <oi*@vinegar.co m> 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(vRou ndTime * 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...mayb e I should.
Function RT(vRoundTime As Date, intMinutes As Integer) As Variant
Dim intVal As Integer
intVal = (24 * 60) / intMinutes
RT = CVDate(Int(vRou ndTime * 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((vRo undTime * 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 RoundTimeNeares tMinutes(vRound Time 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(blDirect ion, 1)) = 1 Then
' round it up
RoundTimeNeares tMinutes = CVDate(Int((vRo undTime * intInterval +
1)) / intInterval)
Else
' round it down
RoundTimeNeares tMinutes = CVDate(-Int((vRoundTime * intInterval)) /
intInterval)
End If
End Function
Glad you got it to work to your satisfaction. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Penn Markham |
last post by:
Hello all,
I am writing a script where I need to use the system() function to call
htpasswd. I can do this just fine on the command line...works great
(see attached file, test.php). When my webserver runs that part of the
script (see attached file, snippet.php), though, it doesn't go through.
I don't get an error message or...
|
by: Ken |
last post by:
hello,
I would to know if it is possible to call an object in a function within a
class.
Meaning , In a class, A function X calling onto a function Y, and function
Y we want one of the two calculation ( eg seconds , out of seconds and
minutes)
thanks ,
Ken
|
by: teddysnips |
last post by:
In the script below is the DDL to create some tables and a UDF.
What I'm interested in is the UDF at the end. Specifically, these few
lines:
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE -- @NumRecords <= 0
If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
|
by: mg |
last post by:
Hi everybody...
We try to white scripts with Pyrhon 2.4 for an acoustic simulation and
we wrote these follow lines :
<begin script>
c = 340
i =j=k= 1
sum_ = 23
table =
|
by: ey.markov |
last post by:
Greetings,
I have an A2K application where for a report the user enters a
month-end date, and the system must gather transactions for that month.
No problem, I thought, I'll just use the DateAdd function for the
beginning of the date range, like this:
Between DateAdd("m",-1,!!) And
!!
| | |
by: Curley Q. |
last post by:
Is there a std lib rounding function that will round a real to a
given number of decimal places? Something like:
double round_it(double number, int decimal_digits)
pass 34.5678, 3 to it and it returns 34.568
|
by: karthi |
last post by:
hi,
I need user defined function that converts string to float in c.
since the library function atof and strtod occupies large space in
my processor memory I can't use it in my code.
regards,
Karthi
|
by: =?Utf-8?B?UmVuZQ==?= |
last post by:
Hello everyone
I have a problem with Math.Round, it´s ocurring some strange:
Math.Round(12.985) = 12.98, it´s wrong. It should be: 12.99
Why?? What is the problem?
Help ME !!!!
|
by: dwasbig9 |
last post by:
Hi Group (fairly limited knowledge of Access and almost none of Access
VBA. Using Access 2003).
I need to sum time, I've found through the groups archive an sql
extract that led me to this
SELECT Format(Sum(Table2.time),"Short Time") AS SumOftime
FROM Table2;
Which works fine but the article also said I would need a Function to
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
