How to Round Time in Access?
in [General]
|
From: Moussa2100 on 27 Feb 2010 06:47 I want to know how to round a time in access or excel ? i see miny topics but it didn't work
From: Tom van Stiphout on 27 Feb 2010 07:20 On Sat, 27 Feb 2010 03:47:01 -0800, Moussa2100 <Moussa2100(a)discussions.microsoft.com> wrote: 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 >I want to know how to round a time in access or excel ? >i see miny topics but it didn't work
From: Allen Browne on 27 Feb 2010 07:22 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Moussa2100" <Moussa2100(a)discussions.microsoft.com> wrote in message news:233D0651-4BE2-41E5-9DDE-1578E85D1709(a)microsoft.com... > I want to know how to round a time in access or excel ? > i see miny topics but it didn't work >
From: KenSheridan via AccessMonster.com on 27 Feb 2010 09:23 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 Moussa2100 wrote: >I want to know how to round a time in access or excel ? >i see miny topics but it didn't work -- Message posted via http://www.accessmonster.com
From: James A. Fortune on 1 Mar 2010 15:21 On Feb 27, 9:23 am, "KenSheridan via AccessMonster.com" <u51882(a)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 MPAPoster(a)FortuneJames.com 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.
|
Next
|
Last
Pages: 1 2 Prev: Cannot find ODBC driver for MS Access 2007 database on Win 7 64-bi Next: hi |