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:
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.
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:
Code:
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.
Last edited: