From: Bre-x on 18 May 2010 13:03 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
From: John Spencer on 18 May 2010 14:55 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 > > >
From: John W. Vinson on 18 May 2010 15:35 On Tue, 18 May 2010 11:03:17 -0600, "Bre-x" <cholotron(a)hotmail.com> 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]
From: Bre-x on 18 May 2010 16:00 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:idp5v5p7tso7l3eag3tnsqm8ahs44knf8j(a)4ax.com... > On Tue, 18 May 2010 11:03:17 -0600, "Bre-x" <cholotron(a)hotmail.com> 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]
From: Bre-x on 18 May 2010 16:02
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" <spencer(a)chpdm.edu> wrote in message news:uBoytur9KHA.420(a)TK2MSFTNGP02.phx.gbl... > 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 >> >> |