How to Round Time in Access?

[Moussa2100]

I want to know how to round a time in access or excel ?
i see miny topics but it didn't work

 

[Tom van Stiphout]

On Sat, 27 Feb 2010 03:47:01 -0800, Moussa2100

You have to ask a more specific question if you want a specific
answer. What kind of value do you have? What kind of rounded value
would you like to have?
Did you check out the DateSerial and TimeSerial functions?

-Tom.
Microsoft Access MVP

 

[Allen Browne]

See:
http://allenbrowne.com/round.html#RoundTime

The article explains several rounding techniques in Accesss, and concludes
with a function you can use to round date/time values to any category
(second, minute, hour, day, etc, or 10 minutes, 15 minutes, etc.)

 

[James A. Fortune]

Our resident mathematical genius James Fortune recently posted a very clever
solution for rounding. His idea can be wrapped in a little function for
Access like so:

Public Function RoundTo(dblVal As Double, dblTo As Double, Optional intUpDown
As Integer = -1) As Double

' rounds up by default.
' to round down pass 1 into function as
' optional intUpDown argument.
RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

As the date/time data type is implemented a 64 bit floating point number,you
can use it for time, e.g. to round up the current date/time to the nearest 5
minutes:

Format(RoundTo(Now(),#00:05:00#),"dd mmmm yyyy hh:nn:ss")

which as I write at 14:17:44 currently returns:

27 February 2010 14:20:00

or:

Format(RoundTo(Now(),#00:05:00#,1),"dd mmmm yyyy hh:nn:ss")

to round down, which as I write returns:

27 February 2010 14:15:00

The value passed into the function as the dblTo argument would normally be an
even divisor of a minute, hour or day of course.

For Excel the equivalent formula would be, to round up:

=-INT(A1 / -(1/288)) * (1/288)

or to round down:

=INT(A1 / (1/288)) * (1/288)

where the date/time value is in A1. 1/288 is 5 minutes as fraction of a day
(24*12 = 288).

Ken Sheridan
Stafford, England

I can't take credit for the original idea. I simply recognized the
genius of it. However, at the risk of offending the first poster in
this NG who offered that idea (I believe it was MVP Van T. Dinh), I
suspect that he borrowed it from some other clever person. I posted a
reply that showed that the technique is valid for all real numbers and
how to apply the technique for different roundup values.

James A. Fortune
(e-mail address removed)

My cosmology was profoundly affected by an early paper written by
Australian Ph. D. Physicist Barry Setterfield a couple of decades
ago. Albert Einstein proved in his Theory of Relativity that the
speed of light is uniform throughout space. Dr. Setterfield
conjectured that the speed of light might not have been uniform in
time. He compiled a list of the measurements of light from many
physicists, including some very renowned physicists, starting with the
estimate of the speed of light using, IIRC, an eclipse of the moons of
Jupiter. He included the physicists' own estimate of their error
bounds of the measurement and showed that a constant speed of light
would violate many of the physicists' estimates of their error
bounds. A graph of the measurements seemed to imply the possibility
that the speed of light has changed over time. He decided to solve
Schrödinger's Equation with the assumption that the speed of light
might vary with time. His solution showed that a decaying speed of
light is theoretically possible and enabled him to come up with a
functional form governing the hypothetical decay. Furthermore, the
theoretical functional form derived from Schrödinger's Equation fit
the curve obtained from the physicists' measurements! He went on to
explain that a decay in the speed of light with time would help
explain the red shift. I do not cite his hypothesis as a proof or
disproof of any controversial scientific theories or religious creeds,
but simply note that neither science nor religion seem to have
adequate answers for all of the questions and that entertaining the
possible ramifications of his hypothesis is a fascinating mental
exercise.

 

[De Jager]

Our resident mathematical genius James Fortune recently posted a very
clever
solution for rounding. His idea can be wrapped in a little function for
Access like so:

Public Function RoundTo(dblVal As Double, dblTo As Double, Optional
intUpDown
As Integer = -1) As Double

' rounds up by default.
' to round down pass 1 into function as
' optional intUpDown argument.
RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

As the date/time data type is implemented a 64 bit floating point number,
you
can use it for time, e.g. to round up the current date/time to the nearest
5
minutes:

Format(RoundTo(Now(),#00:05:00#),"dd mmmm yyyy hh:nn:ss")

which as I write at 14:17:44 currently returns:

27 February 2010 14:20:00

or:

Format(RoundTo(Now(),#00:05:00#,1),"dd mmmm yyyy hh:nn:ss")

to round down, which as I write returns:

27 February 2010 14:15:00

The value passed into the function as the dblTo argument would normally be
an
even divisor of a minute, hour or day of course.

For Excel the equivalent formula would be, to round up:

=-INT(A1 / -(1/288)) * (1/288)

or to round down:

=INT(A1 / (1/288)) * (1/288)

where the date/time value is in A1. 1/288 is 5 minutes as fraction of a
day
(24*12 = 288).

Ken Sheridan
Stafford, England

I can't take credit for the original idea. I simply recognized the
genius of it. However, at the risk of offending the first poster in
this NG who offered that idea (I believe it was MVP Van T. Dinh), I
suspect that he borrowed it from some other clever person. I posted a
reply that showed that the technique is valid for all real numbers and
how to apply the technique for different roundup values.

James A. Fortune
(e-mail address removed)

My cosmology was profoundly affected by an early paper written by
Australian Ph. D. Physicist Barry Setterfield a couple of decades
ago. Albert Einstein proved in his Theory of Relativity that the
speed of light is uniform throughout space. Dr. Setterfield
conjectured that the speed of light might not have been uniform in
time. He compiled a list of the measurements of light from many
physicists, including some very renowned physicists, starting with the
estimate of the speed of light using, IIRC, an eclipse of the moons of
Jupiter. He included the physicists' own estimate of their error
bounds of the measurement and showed that a constant speed of light
would violate many of the physicists' estimates of their error
bounds. A graph of the measurements seemed to imply the possibility
that the speed of light has changed over time. He decided to solve
Schrödinger's Equation with the assumption that the speed of light
might vary with time. His solution showed that a decaying speed of
light is theoretically possible and enabled him to come up with a
functional form governing the hypothetical decay. Furthermore, the
theoretical functional form derived from Schrödinger's Equation fit
the curve obtained from the physicists' measurements! He went on to
explain that a decay in the speed of light with time would help
explain the red shift. I do not cite his hypothesis as a proof or
disproof of any controversial scientific theories or religious creeds,
but simply note that neither science nor religion seem to have
adequate answers for all of the questions and that entertaining the
possible ramifications of his hypothesis is a fascinating mental
exercise.

 

[joelgeraldine]

jj;;;;;vv

"James A. Fortune" <[email protected]> a écrit dans le message de
groupe de discussion :
(e-mail address removed)...

 

[Neil Jones]

On Feb 27, 9:23 am, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
> Our resident mathematical genius James Fortune recently posted a very clever
> solution for rounding. His idea can be wrapped in a little function for
> Access like so:
>
> Public Function RoundTo(dblVal As Double, dblTo As Double, Optional intUpDown
> As Integer = -1) As Double
>
> ' rounds up by default.
> ' to round down pass 1 into function as
> ' optional intUpDown argument.
> RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo
>
> End Function
>
> As the date/time data type is implemented a 64 bit floating point number,you
> can use it for time, e.g. to round up the current date/time to the nearest 5
> minutes:
>
> Format(RoundTo(Now(),#00:05:00#),"dd mmmm yyyy hh:nn:ss")
>
> which as I write at 14:17:44 currently returns:
>
> 27 February 2010 14:20:00
>
> or:
>
> Format(RoundTo(Now(),#00:05:00#,1),"dd mmmm yyyy hh:nn:ss")
>
> to round down, which as I write returns:
>
> 27 February 2010 14:15:00
>
> The value passed into the function as the dblTo argument would normally be an
> even divisor of a minute, hour or day of course.
>
> For Excel the equivalent formula would be, to round up:
>
> =-INT(A1 / -(1/288)) * (1/288)
>
> or to round down:
>
> =INT(A1 / (1/288)) * (1/288)
>
> where the date/time value is in A1. 1/288 is 5 minutes as fraction of a day
> (24*12 = 288).
>
> Ken Sheridan
> Stafford, England

I can't take credit for the original idea. I simply recognized the
genius of it. However, at the risk of offending the first poster in
this NG who offered that idea (I believe it was MVP Van T. Dinh), I
suspect that he borrowed it from some other clever person. I posted a
reply that showed that the technique is valid for all real numbers and
how to apply the technique for different roundup values.

James A. Fortune
(e-mail address removed)

My cosmology was profoundly affected by an early paper written by
Australian Ph. D. Physicist Barry Setterfield a couple of decades
ago. Albert Einstein proved in his Theory of Relativity that the
speed of light is uniform throughout space. Dr. Setterfield
conjectured that the speed of light might not have been uniform in
time. He compiled a list of the measurements of light from many
physicists, including some very renowned physicists, starting with the
estimate of the speed of light using, IIRC, an eclipse of the moons of
Jupiter. He included the physicists' own estimate of their error
bounds of the measurement and showed that a constant speed of light
would violate many of the physicists' estimates of their error
bounds. A graph of the measurements seemed to imply the possibility
that the speed of light has changed over time. He decided to solve
Schrödinger's Equation with the assumption that the speed of light
might vary with time. His solution showed that a decaying speed of
light is theoretically possible and enabled him to come up with a
functional form governing the hypothetical decay. Furthermore, the
theoretical functional form derived from Schrödinger's Equation fit
the curve obtained from the physicists' measurements! He went on to
explain that a decay in the speed of light with time would help
explain the red shift. I do not cite his hypothesis as a proof or
disproof of any controversial scientific theories or religious creeds,
but simply note that neither science nor religion seem to have
adequate answers for all of the questions and that entertaining the
possible ramifications of his hypothesis is a fascinating mental
exercise.

Thank you so much! I found this to be very useful.