Brent Sweet said:
I am making a database that logs the times spent on activites. I have
everything working except we want the time to round to 15 minute
intervals. For example if I am helping someone else with their
project atfrom 8:54 to 9:03 I need this to display in either 8:45 to
9:00 or 9:00 to 9:15. I have searched everywhere for a wat to get
the time to increment like this but haven't found anything.
I use a text box with the =time() and also in an append query which
appends to my logtable <=the table which I log the user and the
activity
Is there any easy way to get =time() to be in 15 min increments?
Here's a function I just threw together to round or truncate a time or
date+time to quarter-hour intervals. I haven't thoroughly tested it,
and I'm not sure it will work right for dates before the "zero date" of
December 30, 1899. However, you can try it out and see how it works for
you. Let me know if you find any bugs.
'----- start of function code -----
Function fncRoundToQuarterHour( _
pTime As Variant, _
Optional RoundMethod As Integer) _
As Variant
' Round a given time to the quarter hour.
'
' Arguments:
' pTime - a variant that can be interpreted as a time,
' or as a date and time. May be Null, yielding
Null.
' RoundMethod - optional integer telling how the rounding is to
' be performed:
' 0 = round to nearest quarter hour
' 1 = round down (truncate)
' 2 = round up
'
' Copyright © Dirk Goldgar, 2004
' License is granted to use this code in your programs and to
' distribute it freely, so long as the copyright notice remains
' intact.
Dim dtGiven As Date
Dim dtDate As Date
Dim dtTime As Date
Dim dblQtrHours As Double
Dim intQtrHours As Integer
Const QTRHOURSPERDAY As Integer = 96
If IsNull(pTime) Then Exit Function
If Not IsDate(pTime) Then Err.Raise 5
dtGiven = CDate(pTime)
dtDate = Fix(dtGiven)
dtTime = dtGiven - dtDate
dblQtrHours = CDbl(dtTime) * QTRHOURSPERDAY
Select Case RoundMethod
Case 0 ' round to nearest
intQtrHours = Round(dblQtrHours, 0)
Case 1 ' round down (truncate)
intQtrHours = Int(dblQtrHours)
Case 2 ' round up
intQtrHours = Int(dblQtrHours)
If (dblQtrHours - intQtrHours) > 0.00001 Then
intQtrHours = intQtrHours + 1
End If
End Select
fncRoundToQuarterHour = _
dtDate + (CDbl(intQtrHours) / QTRHOURSPERDAY)
End Function
'----- end of function code -----