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
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] |
#4
|
|||
|
|||
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] |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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] |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Rounding Time
Hi,
Public Sub temp() Dim the_value As Date the_value = DateValue("05/18/2010 8:16:01") MsgBox RoundTime(the_value, 15, True) End Sub The msgbox shows "05/18/2010" It should show "05/18/2010 8:30" I must be missing something here!!! "John W. Vinson" wrote in message ... 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] |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|