If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Round Time in Access?
I want to know how to round a time in access or excel ?
i see miny topics but it didn't work |
#2
|
|||
|
|||
How to Round Time in Access?
On Sat, 27 Feb 2010 03:47:01 -0800, Moussa2100
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 |
#3
|
|||
|
|||
How to Round Time in Access?
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" wrote in message ... I want to know how to round a time in access or excel ? i see miny topics but it didn't work |
#4
|
|||
|
|||
How to Round Time in Access?
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 |
#5
|
|||
|
|||
How to Round Time in Access?
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 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. |
#6
|
|||
|
|||
How to Round Time in Access?
"James A. Fortune" wrote in message ... 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 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. |
Thread Tools | |
Display Modes | |
|
|