Rounding Time

[Bre-x]

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

[John Spencer]

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

Post by Bre-x
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

[Bre-x]

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?

Post by John Spencer
Our resident mathematical genius James Fortune recently posted a very
clever solution for rounding. His idea can be wrapped in a little
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
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

Post by Bre-x
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

[John Spencer]

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

Post by Bre-x
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?

Post by John Spencer
Our resident mathematical genius James Fortune recently posted a very
clever solution for rounding. His idea can be wrapped in a little
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
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

Post by Bre-x
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

[Bre-x]

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?

Post by John Spencer
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

[Douglas J. Steele]

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!)

Post by Bre-x
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?

Post by John Spencer
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

[KenSheridan via AccessMonster.com]

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

Post by Bre-x
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?

Post by John Spencer
Literal example
CDate(Roundto(#8:14:59#,#00:15:00#))

[quoted text clipped - 3 lines]

Post by John Spencer
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

[Bre-x]

Thank you Ken and Douglas

I did change the dblVal to a Date, and wooaaaallllllaaaaa It works!!!

Thank you once again

Bre-x

Post by KenSheridan via AccessMonster.com
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
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
CDate(RoundToInterval(#8:14:59#,#00:15:00#))
CDate(RoundToInterval(#8:14:59#,#00:15:00#,False))
Ken Sheridan
Stafford, England

Post by Bre-x
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?

Post by John Spencer
Literal example
CDate(Roundto(#8:14:59#,#00:15:00#))

[quoted text clipped - 3 lines]

Post by John Spencer
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/Forums.aspx/access-gettingstarted/201005/1

[John W. Vinson]

Post by Bre-x
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

[Bre-x]

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?

Post by John W. Vinson

Post by Bre-x
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
' 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]

[John W. Vinson]

Post by Bre-x
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?

[Bre-x]

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!!!

Post by John W. Vinson

Post by Bre-x
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]

[John Spencer]

Yes, DateValue returns ONLY the date portion of the string as a DateTime
value. TimeValue returns 0NLY the Time portion of the string.

If you want to convert the entire string and have both the date and time
values use CDate to the do the conversion.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Post by Bre-x
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!!!

Post by John W. Vinson

Post by Bre-x
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]

[Bre-x]

Thank you John
It works very well!!!!

Post by John Spencer
Yes, DateValue returns ONLY the date portion of the string as a DateTime
value. TimeValue returns 0NLY the Time portion of the string.
If you want to convert the entire string and have both the date and time
values use CDate to the do the conversion.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Post by Bre-x
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!!!

Post by John W. Vinson

Post by Bre-x
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]