Datediff

[Guest]

I am trying to use this function to round the difference between 2 times to
the nearest quarter hour, but if the criteria below is used for rounding,
what adjustment do I need to make to the function? (thanks)

Function:
Round((DateDiff("n", Me![Start], Me![End]) - 1) / 60 / 0.25, 0) * 0.25

Actual minutes difference table
:54 - :08 => .00
:09 - :23 => .25
:24 - :38 => .50
:39 - :53 => .75

 

[Marshall Barton]

I am trying to use this function to round the difference between 2 times to
the nearest quarter hour, but if the criteria below is used for rounding,
what adjustment do I need to make to the function? (thanks)

Function:
Round((DateDiff("n", Me![Start], Me![End]) - 1) / 60 / 0.25, 0) * 0.25

Actual minutes difference table
:54 - :08 => .00
:09 - :23 => .25
:24 - :38 => .50
:39 - :53 => .75

To round to quarter hours, you can use:
Round(DateDiff("n", #1:37#, #1:59#) / 15) * 15

I'm not at all sure I understand what your "difference
table" is for, but if that's supposed to be the definition
of how to "round", then I think you need to create your own
function:

Public Function MyRound(dtStart, dtEnd)
Dim mins As Long, x As Long

mins = DateDiff("n", dtStart, dtEnd)
Select Case mins Mod 60
Case 9 To 23
x = 15
Case 24 To 38
x = 30
Case 39 To 53
x = 45
Case Else
x = 0
End Select

MyRound = mins \ 60 + x
End Function