Round to the nearest quarter (15 minutes) (1 Viewer)

[KitaYama]

I don't know the correct terminology, so the title may be a little off.

I have a calculated field in a query that rounds a date/time value up to the nearest 15 minutes:
DtIn: ClockIn([StartedFrom])
2023/01/20 01:32:00 will be returned as 2023/01/20 01:45:00

I have another field that rounds down the given date/time to nearest 15 minute:
DtOut: Clockout([EndedOn])
2023/01/20 01:28:00 will be returned as 2023/01/20 01:15:00

These two calculated fields are later used in other calculations.
The following is what I've ended to for the first one:

Public Function ClockIn(t As Date) As Date
  
    Dim Diff As Integer
    Dim MinIn As Integer
          
    MinIn = Minute(t)
    Select Case MinIn
        Case 0 To 14
            Diff = 15 - MinIn
      
        Case 15 To 29
            Diff = 30 - MinIn
      
        Case 30 To 44
            Diff = 45 - MinIn
      
        Case 45 To 59
            Diff = 60 - MinIn
      
    End Select
        
    ClockIn = DateAdd("n", Diff, t)
  
End Function

I'm very new to working with date/time and just wanted to be sure I'm taking the best shot.
Is there a better, more robust way to do the same.

Thank you.

 

[plog]

I don't know what you mean by "robust", but there's probably at least 13 different ways (hear that Access World? That's another Plog's 'Show-Me-Your*-An-Overachiever' Simple Coding Challenge) to accomplish what you did. And your's is perfectly fine.

The only critique would be that you need to add comments to it. Put a line telling its purpose, then when you go into the Select block explain what you are doing. Always comment code--it will help others and your future self.

Now, while I don't submit code to these challenges (don't worry, you will soon be drowing in people trying to outdo each other), I will tell you that you can probably get it down to 5 total lines by using the MOD() function:

MS Access: Mod Function

This MSAccess tutorial explains how to use the Access Mod function with syntax and examples. The Microsoft Access Mod function returns the remainder after a number is divided by a divisor.

www.techonthenet.com

* please point this out for bonus points

 

[CJ_London]

I might take up the challenge but about to hit the sack. I would suggest you can use the same round up code for the clock out - just deduct 15 minutes either when passing the parameter or from the result

 

[KitaYama]

don't worry, you will soon be drowing in people trying to outdo each other

It's one of those occasions when I'm satisficed to be here. I like to see all my options, and choose one that suits me best. Some members may see it as a competition or something, but for me it's a carnival to see how the same task can be done in different ways. It helps me to learn how to attack a wall from different directions the next time I face a problem.

I will tell you that you can probably get it down to 5 total lines by using the MOD() function

I knew about MOD, but never thought of it.
I think this is simpler than my original code:

Public Function ClockIn(t As Date) As Date

    Dim Diff As Integer

    Diff = Minute(t) Mod 15
    CalIn = DateAdd("n", 15 - Diff, t)
 
End Function

I will add the comments per suggestion.

* please point this out for bonus points

Sorry, I didn't get it.

Thanks for your help.

 

[KitaYama]

I might take up the challenge but about to hit the sack. I would suggest you can use the same round up code for the clock out - just deduct 15 minutes either when passing the parameter or from the result

I'll be waiting for the time you're back.
I have the clockout function. I simply didn't want to make the question too long and hard to understand.

Thank you.

 

[MajP]

I'm very new to working with date/time and just wanted to be sure I'm taking the best shot.

Helpful functions to know

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

TimeSerial function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

DateValue function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

TimeValue function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[arnelgp]

you can use Ceiling() (round Up) or Floor() (round down)

copy paste the code and run test() sub.
result:

1/1/2022 3:30:00 PM
1/1/2022 3:15:00 PM

you can create your own "rule" if you want it to automatically round up or round down.

Private Sub test()
Const min_15 As Double = 1.04166666666667E-02
dim a, b
dim dt as date, finalResult as date
dt=#1/1/2022 3:25:00 PM#


a= CDate(Ceiling(dt, min_15))
b= CDate(Floor(dt, min_15))

finalresult = b
if a-dt > b-dt then
    finalresult = a
end if

'return finalresult
End Sub

' https://www.tek-tips.com/faqs.cfm?fid=5031
Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function
' https://www.tek-tips.com/faqs.cfm?fid=5031
Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Floor = Int(X / Factor) * Factor
End Function

 

[MarkK]

Function RoundDateToNearest(d1 As Date, RoundTo As Date) As Date
    RoundDateToNearest = CLng(d1 / CDbl(RoundTo)) * RoundTo
End Function

Debug.Print RoundDateToNearest(now, #00:15#)

 

[arnelgp]

similar to Floor().