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
|
|||
|
|||
Rounding Time
Hi,
I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x |
#2
|
|||
|
|||
Rounding Time
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 You can use that to round up the time as follows: CDate(Roundto([Punch In],#00:15:00#)) And to round down CDate(Roundto([Punch Out],#00:15:00#,1)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-x wrote: Hi, I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x |
#3
|
|||
|
|||
Rounding Time
Thank you for answering my post.
I dont understand. What values how to I pass them to the function? CDate(Roundto([Punch In],#00:15:00#)) punch in is the date? "John Spencer" wrote in message ... 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 You can use that to round up the time as follows: CDate(Roundto([Punch In],#00:15:00#)) And to round down CDate(Roundto([Punch Out],#00:15:00#,1)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-x wrote: Hi, I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x |
#4
|
|||
|
|||
Rounding Time
Replace Punch In and Punch Out with the name of your field or the name of a
variable that contains a DateTime value or a literal DateTime value. Be sure that you do NOT pass the function a NULL (blank) value. If you do you will get an error. Literal example CDate(Roundto(#8:14:59#,#00:15:00#)) Variable example Dim dTime as Date dTime = #8:14:59# CDate(Roundto(dTime,#00:15:00#)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-x wrote: Thank you for answering my post. I dont understand. What values how to I pass them to the function? CDate(Roundto([Punch In],#00:15:00#)) punch in is the date? "John Spencer" wrote in message ... 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 You can use that to round up the time as follows: CDate(Roundto([Punch In],#00:15:00#)) And to round down CDate(Roundto([Punch Out],#00:15:00#,1)) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-x wrote: Hi, I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x |
#5
|
|||
|
|||
Rounding Time
Hi
You are declaring the dTime as a Date Variable the first variable that the RoundTo Function is expecting is a Double I am missing something here? Literal example CDate(Roundto(#8:14:59#,#00:15:00#)) Variable example Dim dTime as Date dTime = #8:14:59# CDate(Roundto(dTime,#00:15:00#)) 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 |
#6
|
|||
|
|||
Rounding Time
Internally, a Date variable is an 8 byte floating point number. In other
words, it's a Double. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi You are declaring the dTime as a Date Variable the first variable that the RoundTo Function is expecting is a Double I am missing something here? Literal example CDate(Roundto(#8:14:59#,#00:15:00#)) Variable example Dim dTime as Date dTime = #8:14:59# CDate(Roundto(dTime,#00:15:00#)) 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 |
#7
|
|||
|
|||
Rounding Time
The function is designed to cater for any numeric data type, not just
date/time values, which is why its arguments are declared as Double. In your case it just so happens that you dealing with date/time values, so it make sense to declare the variable for the value to be passed to the function as Date. A date/time value is implemented as a 64 bit floating point number in fact; we just normally see it in a date/time format. In the original thread in which I posted this function there was subsequent input from David Fenton, who made pertinent points about the advisability of breaking the expression down and assigning the result of each constituent operation to its own variable to avoid rounding errors. This lead to the amendment of the function to: Public Function RoundToInterval(dblVal As Double, _ dblTo As Double, _ Optional blnUp As Boolean = True) As Double ' rounds up by default. ' to round down pass False into function as ' optional UpDown argument Dim intUpDown As Integer Dim lngTestValue As Long Dim dblTestValue As Double Dim dblDenominator As Double If blnUp Then intUpDown = -1 Else intUpDown = 1 End If dblDenominator = intUpDown * dblTo dblTestValue = dblVal / dblDenominator lngTestValue = Int(dblTestValue) RoundToInterval = intUpDown * lngTestValue * dblTo End Function It would be called in the same way, e.g. to round up, which is the default: CDate(RoundToInterval(#8:14:59#,#00:15:00#)) or to round down: CDate(RoundToInterval(#8:14:59#,#00:15:00#,False)) Ken Sheridan Stafford, England Bre-x wrote: Hi You are declaring the dTime as a Date Variable the first variable that the RoundTo Function is expecting is a Double I am missing something here? Literal example CDate(Roundto(#8:14:59#,#00:15:00#)) [quoted text clipped - 3 lines] dTime = #8:14:59# CDate(Roundto(dTime,#00:15:00#)) 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#8
|
|||
|
|||
Rounding Time
On Tue, 18 May 2010 11:03:17 -0600, "Bre-x" wrote:
Hi, I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x Just for fun... Public Function RoundTime(dteIn As Date, lngInterval As Long, _ blnUpDown As Boolean) As Date ' Round time to the nearest specified interval in minutes ' Parameters: ' dteIn - input date/time value ' lngInterval - size of desired time block in minutes, ' e.g. 15 = round to 8:00, 8:15, 8:30 ' blnUpDown - Yes/No value: True = round up to the end of the block, ' False = round down ' ' 1440 minutes in a day, divided by the number of minutes in an interval ' Round down to the start of the block, convert back to a date/time RoundTime = CDate(lngInterval * Fix(1440 * CDbl(dteIn) / lngInterval) / 1440) ' add minutes to the end of the interval if needed; if the value is exactly on ' the boundary leave it alone If blnUpDown And RoundTime dteIn Then RoundTime = DateAdd("n", lngInterval, RoundTime) End If End Function -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Rounding Time
Hi, Thank you for answering my post
the RoundTime function is not working or I dont know how to use it Dim the_value As Date the_value = "05/18/2010 8:15:01" msgbox RoundTime(the_value, 15, True) this should show 8:30 Rigth? "John W. Vinson" wrote in message ... On Tue, 18 May 2010 11:03:17 -0600, "Bre-x" wrote: Hi, I am working on a Time Clock. I need some help to create two rouding functions. PUNCH IN TO between 8:00:01 to 8:14:59 8:15 between 8:15:01 to 8:29:59 8:30 between 8:30:01 to 8:44:59 8:45 between 8:45:01 to 8:59:59 9:00 PUNCH OUT TO between 3:00:01 to 3:14:59 3:00 between 3:15:01 to 3:29:59 3:15 between 3:30:01 to 3:44:59 3:30 between 3:45:01 to 3:59:59 3:45 Thank you All!!! Bre-x Just for fun... Public Function RoundTime(dteIn As Date, lngInterval As Long, _ blnUpDown As Boolean) As Date ' Round time to the nearest specified interval in minutes ' Parameters: ' dteIn - input date/time value ' lngInterval - size of desired time block in minutes, ' e.g. 15 = round to 8:00, 8:15, 8:30 ' blnUpDown - Yes/No value: True = round up to the end of the block, ' False = round down ' ' 1440 minutes in a day, divided by the number of minutes in an interval ' Round down to the start of the block, convert back to a date/time RoundTime = CDate(lngInterval * Fix(1440 * CDbl(dteIn) / lngInterval) / 1440) ' add minutes to the end of the interval if needed; if the value is exactly on ' the boundary leave it alone If blnUpDown And RoundTime dteIn Then RoundTime = DateAdd("n", lngInterval, RoundTime) End If End Function -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Rounding Time
On Tue, 18 May 2010 14:00:18 -0600, "Bre-x" wrote:
Hi, Thank you for answering my post the RoundTime function is not working or I dont know how to use it Dim the_value As Date the_value = "05/18/2010 8:15:01" msgbox RoundTime(the_value, 15, True) this should show 8:30 Rigth? You're passing it a text string. It's expecting a Date/Time value. Try the_value = DateValue("05/18/2010 8:15:01") If it doesn't work (it did for me...) post back with more details. What happened? No result, wrong result, flames coming out of your monitor? -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|