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

 

[John W. Vinson]

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?

 

[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?

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

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 W. Vinson]

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

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?

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

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,

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

 

[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?

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.

 

[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

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

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

 

[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

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

 

[Bre-x]

Thank you John
It works very well!!!!

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