ROUND OFF TIME 2

[jadams0173]

I'm trying to expand on this formula that SkipVought provided in Thread705-856404

The formula is
=INT((now()*MinPerDay+Precision/2)/Precision)*Precision/MinPerDay

Precision=15
MinPerDay=60 * 24

What I'm trying to do is, if the time is 1:00 round it to 1:15. It it's 1:17 round it to 1:30. Basically 15 minutes past each hour depending on the current system time.

 

[PHV]

Perhaps this ?
=(1+Int(Now()*96))/96

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

As usual PHV you are correct. That works. Can you take a minute and explain how you come up with the forumula? I tried in vain for quite some time to make sense of it.

 

[PHV]

In fact 96 is the number of quarters of hour in a day (24 * 4).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Thanks again for getting back to me. So if I understand correctly, say I wanted every 30 minutes.

that would be (24*2)

or how about 20 minutes:

that would be (24*3)

Am I understanding correctly?

 

[PHV]

I think so.

 

[MichaelRed]

mayhap a bit much for some:

Public Function basRnd2Val(ValIn As Single, _
                           Rnd2 As Single, _
                           Optional RndDn As Boolean = False) As Single

    'Michael Red    11/27/02    Tek-Tips thread701-414063
    'Modified 1/31/2003 to Include optional Round Down
    'for NAVAJAVB re Tek-Tips thread701-462531

    '? basRnd2Val(183.33, 10, True)
    '180


    '? basRnd2Val(7.49, 3.625)
    ' 10.875

    '? basRnd2Val(7.49, 3)
    ' 9

    '? basRnd2Val(7.49, 0.01)
    '7.5

    Dim ModRmdr As Single
    Dim ValPart() As String
    Dim DecPart() As String
    Dim Shift(2) As Integer
    Dim MyVal As Long
    Dim MyRnd As Long
    Dim MyRtn As Long

    ValPart = Split(Str(ValIn), ".")
    DecPart = Split(Str(Rnd2), ".")

    If (UBound(ValPart) > 0) Then
        Shift(0) = Len(ValPart(1))
     Else
        Shift(0) = 0
    End If

    If (UBound(DecPart) > 0) Then
        Shift(1) = Len(DecPart(1))
     Else
        Shift(1) = 0
    End If

    If (Shift(0) > Shift(1)) Then
        Shift(2) = Shift(0)
     Else
        Shift(2) = Shift(1)
    End If

    MyVal = Val(ValIn) * (10 ^ Shift(2))
    MyRnd = Val(Rnd2) * (10 ^ Shift(2))

    MyRtn = MyVal - (MyVal Mod MyRnd) + MyRnd
    basRnd2Val = MyRtn * 10 ^ (-1 * Shift(2))

    If (RndDn = True) Then
        basRnd2Val = basRnd2Val - Rnd2
    End If

End Function

and. of course. a few relevant (to the topic of the thread) examples:

? basRnd2Val(#2:25#, #0:30#)
 0.1041666 

? Format(basRnd2Val(#2:25#, #0:30#), "Short Time")
02:30

? Format(basRnd2Val(#2:25#, #0:15#), "Short Time")
02:30

? Format(basRnd2Val(#2:25#, #0:15#, -1), "Short Time")
02:15

MichaelRed

 

[jadams0173]

Thanks for the code Michael.