Round to nearest quarter number

[twoody54]

Hi,

I have a calculation that gives an budgeted hour mark. Because it is calculated the numbers come out to things like 9.77 instead of 9.75 hours. We record our time based upon quarter hours so I need something that will round it to the nearest quarter number .0, .25, .5, .75.

to sum up I need these types of conversions
1.2=1.25
1.05 = 1.0
1.65= 1.75
etc
etc

Any help is greatly appreciated.

 

[HitechUser]

twoody54,

Maybe this...

'Double format
Hours = CDbl( CLng( Hours * 4 ) )/ 4.0

 

[HitechUser]

Sorry, it won't work if you are rounding down...

 

[HitechUser]

Create your own function and pass in the minutes. then add to the total "whole" number of hours. Make sure to add one hour if over 53 minutes....

'Sample code not complete
..
    Case varminute < 8
            qtrmin = 0
     Case varminute > 7 and varminute < 23
            qtrmin = 15
     Case varminute > 22 and varminute < 38
            qtrmin = 30
     Case varminute > 37 and varminute < 53
            qtrmin = 45
     Case varminute > 52 and varminute < 60
            qtrmin = 0
    End Select
...
.
.

Just an idea...

 

[sfreeman]

How about this...

http://support.microsoft.com/default.aspx?scid=kb;en-us;209996

Sam_F
"90% of the problem is asking the right question.

 

[twoody54]

No luck. The numbers are off.

http://support.microsoft.com/default.aspx?scid=kb;en-us;155696

This almost does the trick other than it always rounds down or always rounds up...

 

[twoody54]

Hitechuser,

I did basically what you suggested and got it working. So far it appears to be doing the trick. Here's the function I wrote:

Public Function RoundToNearest(Amt As Double) As Double

Dim remainder As Double
remainder = Amt - Int(Amt)

Dim Temp As Double
Temp = Amt / 0.25
If Int(Temp) = Temp Then
RoundToNearest = Amt
Else
Select Case remainder
Case Is <= 0.125
remainder = 0
Case Is <= 0.375
remainder = 0.25
Case Is <= 0.625
remainder = 0.5
Case Is <= 0.875
remainder = 0.75
Case Else
remainder = 1
End Select
RoundToNearest = Int(Amt) + remainder
End If
End Function

 

[dc20]

This works in excel

=IF((F5-TRUNC(F5))<0.125,TRUNC(F5),IF((F5-TRUNC(F5))<0.375,TRUNC(F5)+0.25,IF((F5-TRUNC(F5))<0.625,TRUNC(F5)+0.5,IF((F5-TRUNC(F5))<0.875,TRUNC(F5)+0.75,TRUNC(F5)+1))))

 

[twoody54]

Yeah, I also found a function called MROUND that comes with the Analysis Toolpak that works exactly how I want it in Excel, but unfortunately it doesn't work in VBA coding for access.