Rounding to the nearest X Minutes

[Steve_God]

Hi,

Is there a simple way of getting access to round the time to the nearest 5 or 10 minutes

Thanks

 

[Travis]

Here is a function that will convert the time to the nearest 5 or 10.

Public Function RoundTime(ByVal dTime As Date) As Date
    Dim min As String
    Dim rnd As Integer
    Dim add As Double
    
        min = DatePart("n", dTime)
        rnd = Right(min, 1)
        add = 0
        
        Select Case rnd
            Case 0, 5: add = 0
            Case 1, 6: add = -1
            Case 2, 7: add = -2
            Case 3, 8: add = 2
            Case 4, 9: add = 1
        End Select
        
        RoundTime = DateAdd("n", add, dTime)
    
End Function

 

[ewc]

For billable hours, I use the following. Replace both "15's" to any other time (30, 60 etc) for other rounding schemes. You also need to edit the half-time to add (7.49) if you use a different round up number (30 would use 14.99 as a half time (roundnumber/2 - .01)).

- ActivityTotal is the actual calculation I started with doing date math.
- TotalRnd is the recalculated rounded number

1) To round UP to the nearest 15 minutes use:
Dim NewTime As Integer
NewTime = 15 * CInt((ActivityTotal + 7.49) / 15)
TotalRnd = NewTime

2) To round up OR down to the nearest 15 minutes:
Dim NewTime As Integer
NewTime = 15 * CInt((ActivityTotal) / 15)
TotalRnd = NewTime