M

#### Moussa2100

I want to know how to round a time in access or excel ?

i see miny topics but it didn't work

i see miny topics but it didn't work

M

I want to know how to round a time in access or excel ?

i see miny topics but it didn't work

i see miny topics but it didn't work

Ad

T

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

A

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

J

I can't take credit for the original idea. I simply recognized theOur 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

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.

D

I can't take credit for the original idea. I simply recognized theOur 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

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.

J

"James A. Fortune" <[email protected]> a écrit dans le message de

groupe de discussion :

(e-mail address removed)...

Ad

- Joined
- Sep 26, 2017

- Messages
- 1

- Reaction score
- 0

Thank you so much! I found this to be very useful.On Feb 27, 9:23 am, "KenSheridan via AccessMonster.com" <[email protected]>

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.

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.