=time() IN 15 minute intervals

B

Brent Sweet

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

John Vinson

On Mon, 8 Nov 2004 07:00:01 -0800, "Brent Sweet" <Brent
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?

Try updating to

CDate(CInt(Time() * 96) / 96)

There are 96 15-minute intervals in a day; CInt will truncate the time
to the most recent previous such interval; dividing by 96 will get the
number back to the range 0 to 1 (days); and CDate will convert this
back to a date/time.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
D

Dirk Goldgar

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

15 minute data to daily data 1
Half hour intervals 5
Averaging time in 15 minute increments 3
15 Minute Intervals in Calendar 2
Numeric increment 4
Time intervals 1
Graph usage for times of day 0
Query or report 5

Top