Datediff

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary Fran - 13 Jul 2007 18:30 GMT

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

Reply to this Message

Marshall Barton - 13 Jul 2007 21:50 GMT

>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,
[quoted text clipped - 8 lines]
>: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

Signature

Marsh
MVP [MS Access]

Reply to this Message