A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rounding Time



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 06:03 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old May 18th, 2010, 07:55 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 18th, 2010, 08:35 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 18th, 2010, 09:00 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old May 18th, 2010, 09:02 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old May 18th, 2010, 11:10 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 19th, 2010, 01:36 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 19th, 2010, 03:32 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old May 19th, 2010, 03:56 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default 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  
Old May 19th, 2010, 06:25 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.