rounding number 3

[budich]

How to round a number to 0.5 not 1.
for example : 7.14 become 7.00
7.16 become 7.30
7.44 become 7.30
7.45 become 8.00
I use this rounding in rounding of number of hours equipments report.
thank you very much in advance

 

[JerryDennison]

Copy and paste the following function into a module.

Function fRound(vInput As Variant, Optional iFactor As Integer) As Double
'This function will round any numeric data type to the number of decimal places specified
'Usage: fRound(value to round, number of decimal places(optional))
On Error Resume Next
If iFactor < 0 Then iFactor = 0 'Trap negative values and assume 0 decimal places
iFactor = 10 ^ Nz(iFactor)
fRound = Int(Nz(vInput) * iFactor + 0.5) / iFactor
End Function

 

[rhicks]

I think what you are trying to do is display 7.30 as 7hrs and 30min. This is not a correct result. 7.5 would be the correct decimal equivalent. If you want to display the result as 7:30 then this would reflect 7hrs and 30 minutes. I'm kinda of confused, could you be more specific? I think what you are trying to do it round to hour/half hour.

RDH

Ricky Hicks
rdhicks@mindspring.com

 

[raskew]

I was initially confused by this posting (and, who knows, may still be) but I think what we're dealing with is not rounding a number, but rather rounding hours and minutes, expressed in a decimal-like format, to the nearest one-half hour.

This is something of a multi-step process. The basics were discussed recently in this thread

http://www.tek-tips.com/viewthread....=45343&amp;cfid=10044834&amp;cftoken=97034057

To avoid confusion, I would want to separate hours and minutes with a colon rather than a period, e.g., 7 hours and 14 minutes would appear as &quot;7:14&quot; (a string) rather than 7.14 (a decimal). Then, in sequence:

(1) Convert string to minutes, rounding minutes to nearest half-hour
(2) Convert minutes to string expression

From the debug window it would look like this:


ConvMins = minround(StringToMinutes(&quot;7:14&quot)
? convmins
420
? MinutesToString(ConvMins)
7:00

***********************************************************************************

Function MinutesToString(ByVal numMins As Integer) As String
'*******************************************
'Name: MinutesToString (Function)
'Purpose: Convert minutes into an hr:min
' string
'Inputs: ? MinutesToString(662)
'Output: 11:02
'*******************************************
Dim strHrs As String
Dim strMins As String

strHrs = Int(numMins / 60)
strMins = Format(numMins Mod 60, &quot;00&quot

MinutesToString = strHrs &amp; &quot;:&quot; &amp; strMins
End Function

Function StringToMinutes(TimeStr As String) As Integer
'*******************************************
'Name: StringToMinutes (Function)
'Purpose: Convert an hr:min string to number
' of minutes (an integer)
'Inputs: ? StringToMinutes(&quot;11:02&quot
'Output: 662
'*******************************************

Dim numHrs As Integer, numMins As Integer
numHrs = Val(Left(TimeStr, InStr(TimeStr, &quot;:&quot - 1))
numMins = Val(Mid(TimeStr, InStr(TimeStr, &quot;:&quot + 1))
StringToMinutes = Val((60 * numHrs) + numMins)
End Function

Function minround(numMins As Integer)
'*******************************************
'Name: minround (Function)
'Purpose: Rounds a number of minutes to
' to the nearest 1/2 hour
'*******************************************

Dim hrpart As Integer, hrperc As Single, minhold As Single

hrpart = numMins Mod 60
hrperc = hrpart / 60
minhold = Switch(hrperc < 0.25, 0, hrperc <= 0.75, 30, True, 60)
minround = 60 * Int(numMins / 60) + minhold

End Function

 

[rhicks]

I think you see this as I do. Here's what I came up with. I set the Case Select statement for the rounding, I hope I understood which way the poster wanted to round the minutes, but these can easily be changed as needed. The result of this function will also be a string value.

'**********************Start Code***************************
Function Format_Hrs_Mins(ByVal Interval As Variant) As String
Dim Hours As Long, Minutes As Long
If IsNull(Interval) Then Exit Function
'
' Parse Hours
Interval = Interval * 24
Hours = Int(Interval)
Interval = Interval - Hours
' Parse Minutes
Interval = Interval * 60
Minutes = Int(Interval)
Interval = Interval - Minutes

If Minutes >= 60 Then
Hours = Hours + 1
Minutes = Minutes - 60
End If

Select Case Minutes
Case 0 To 14
Minutes = 0
Case 15 To 44
Minutes = 30
Case 45 To 59
Minutes = 0
Hours = Hours + 1
End Select

Format_Hrs_Mins = Hours &amp; &quot;:&quot; &amp; Format(Minutes, &quot;00&quot

End Function
'************************End Code***************************

Just an added thought,
RDH

Ricky Hicks
rdhicks@mindspring.com

 

[raskew]

Ricky-

Please provide an example call. I loaded up your function, tried it with ? format_hrs_mins(7.14), which returned 171.30. What did I do wrong?

Bob

 

[rhicks]

Sorry Robert, I guess it easier with and example.

Format_Hrs_Mins([DateTimeEnd]-[DateTimeStart])
(Where the two arguments are Date/Time datatypes)

This is the basic idea.

RDH

Ricky Hicks
rdhicks@mindspring.com

 

[jfhewitt]

It might be simpler to create a lookup table and use the dlookup function

 

[budich]

Thanks my friends,

I am still try one function from Jerry Dennison, but still cannot work. When I applied in my report there was an error message in my object report say &quot;name?&quot;.
The number that I want to be rounded in standard number's format (not in time format), because I have convert working hour to number with function hour and minute.
thank in advance

 

[JerryDennison]

I should've used the &quot;Public&quot; predicate. Also, double check your spelling.

Public Function fRound(vInput As Variant, Optional iFactor As Integer) As Double
'This function will round any numeric data type to the number of decimal places specified
'Usage: fRound(value to round, number of decimal places(optional))
On Error Resume Next
If iFactor < 0 Then iFactor = 0 'Trap negative values and assume 0 decimal places
iFactor = 10 ^ Nz(iFactor)
fRound = Int(Nz(vInput) * iFactor + 0.5) / iFactor
End Function

 

[budich]

Hi Jerry, I am afraid I make mistake in an object report so appear error message, but I have tried again with number label in my report. I understand your logic but it can't produce as I want.
As I change little bit (0.5 -- 0.25),

Function fRound(vInput As Variant, Optional iFactor As Integer) As Double
'This function will round any numeric data type to the number of decimal places specified
'Usage: fRound(value to round, number of decimal places(optional))
On Error Resume Next
If iFactor < 0 Then iFactor = 0 'Trap negative values and assume 0 decimal places
iFactor = 10 ^ Nz(iFactor)
fRound = Int(Nz(vInput) * iFactor + 2.5) / iFactor
End Function

the results are :
9.12 ----- 9.3 not correct, it should be 9.0
9.25 ----- 9.5 correct
9.27 ----- 9.5 correct
9.55 ----- 9.8 not correct, it should be 9.5
9.76 ----- 10.0 correct
9.95 ----- 10.2 not correct, it should be 10

thank you in advance

 

[budich]

I am sorry Raskew if you were confuse.
Basically the idea :
1. I have succesfully convert from working hour to decimal number, for example : 9.13
2. The problem is, I want round the number to the nearest 0.5 so it should be 9.00 (because the required specification need rounded to half an hour/ decimal 0.5).
3. basically this problem only deal with number not time.
thank you

 

[rhicks]

I'm the one that's really confused. The way you are rounding now, is not even close to the examples you gave in the original post. But I stay confused.......

RDH

Ricky Hicks
rdhicks@mindspring.com

 

[RickSpr]

How about this to round &quot;hrs&quot; to the nearest 0.5&quot;

  result = Int(hrs * 2 + .5) / 2

It passes your tests above, and it's very simple.

 

[raskew]

I give up!!

Old saying goes &quot;…you can lead a horse to water but you can't make it drink.&quot; Translated-&quot;…you can provide a working solution but you can't make them load it up and try it, particularly if it looks complicated.&quot;

So, Budlich… 9.13 is a decimal, not a time, you now tell us. Thus, you're saying that 9.13 doesn't mean nine hours and thirteen minutes, but rather nine hours, seven minutes and 48 seconds (thirteen one-hundredths of an hour).

Someone's obviously confused here. I'll just concede and say it's me.

 

[rhicks]

Robert your not the only one.

&quot;Here's another dog named Ring&quot;

RDH

Ricky Hicks
rdhicks@mindspring.com

 

[budich]

Thank's alot RickSpr for your helpfull idea, I have tried and it's work.
For JerryDennison, thank you for your effort.
For Rhicks and Raskew, I am sorry if disappointed you but I don't really mean that. If you see my original post I say :

How to round a number to 0.5 not 1.
for example : 7.14 become 7.00
7.16 become 7.30
7.44 become 7.30
7.45 become 8.00

may be you are confused with my next line and my english also not so good.
but anyway, your posts are helpfull for me, so I have other option to treat the working hour report.
Thank you

 

[raskew]

Budlich, my friend,

Your English is more than adequate. Don't think anyone's confused. It's the logic that's making us crazy!!

 

[JerryDennison]

Yes, Budich, your english is more than sufficient. I'm sorry I misread your very first post and didn't fully understand your question.