Rounding down a number 6.375 to 6.3

[jmccullough]

I am working on a Payroll report and have totaled up the number of "sick days" used. This number for one individual totals up to 6.375. (He has used 6 days and 3 hours). Our work day is equal to 8 hours.
When I use the Round function on 6.375 it displays as 6.4 but I need it to show the actual number of days/hours which would be 6.3

In my report footer I have created a text box named ILL and used the SUM function to get the total sick time used. Then in the detail section of the report I am using the Round function Round([ILL],1), but this gives me the 6.4 and I need 6.3 Is there a way to accomplish this?

Thanks for any assistance.

 

[Steve R.]

Try multiplying the number by 10, putting the value into an integer, which will truncate the remainder, then divide the integer by 10 into a decimal number.

 

[RuralGuy]

I'm a little confused since 6.375 really does round to 6.4. It sounds like you just want to truncate the decimal past one decimal place.

 

[Sess]

To round down you can subtract half of then next decimal place. So you can use
Round([ILL]-0.05,1)
But you should maybe use DatePart function to convert hours to decimal or you are converting 3/8 into 1/3.
((DatePart("n", [ILL]) / 60) + DatePart("H", [ILL]))

 

[dcb]

You are storing Hours so consider the following formula:

?Fix(51/8)&"."&(51-(Fix(51/8)*8))
6.3

?Fix(51/8)&" Days "&(51-(Fix(51/8)*8)) & " Hrs"
6 Days 3 Hrs

 

[jmccullough]

Sess and Steve, tried each of your suggestions and BOTH worked perfectly.
Thanks so much, really appreciate the help.

 

[dcb]

I would normally walk away from this, however being a payroll system, the original OP could get into alot of trouble (at work) if this is wrong! Therefore I ask you to run this code looping through all 4 methods and running Hours from 52 -> 56.

The query was to convert 6.375 as 8 hr days into DAYS.HOURS - EDIT: Only method 4 in the code stands up to all tests

Please Note:

• I am not trying to play anyone down here - please do not be offended
• Sess i may not have used ((DatePart("n", [ILL]) / 60) + DatePart("H", [ILL])) as intended - please correct me if this is the case (I didnt understand the seconds)
• I have used Hours in the loop for ease
• The immediate window [Ctrl]+[G] prints out the results

Public Sub testHours()
    Dim intNumber As Integer
    Dim decNumber As Double
    Dim intHours As Integer
    Dim strLabel As String
    Dim X As Integer
    Dim DH_Notes As String
    
    
    
    For X = 52 To 56 '' **** CHANGE THE HOURS HERE ******
    intHours = X
    
    Debug.Print ""
    Debug.Print "*** USING HOURS: " & intHours
    Debug.Print "Method", "Math", "Result", "Notes"
    Debug.Print "==========", "==========", "==========", "=========="
    
'' Method 1
    strLabel = "Steve R"
    decNumber = intHours / 8
    Debug.Print strLabel, "DecNumber", decNumber
    decNumber = decNumber * 10
    Debug.Print , "X 10", decNumber
    intNumber = Int(decNumber)
    Debug.Print , "int = dec", intNumber
    decNumber = intNumber / 10
    DH_Notes = CStr(Fix(decNumber)) & " Days & " & Right(CStr(decNumber - Fix(decNumber)), 1) & " Hours"
    Debug.Print , "dec OUT", decNumber, DH_Notes
    Debug.Print ""
    
'' Method 2
    strLabel = "Sess M1"
    decNumber = intHours / 8
    Debug.Print strLabel, "DecNumber", decNumber
    decNumber = Round(decNumber - 0.05, 1)
    DH_Notes = CStr(Fix(decNumber)) & " Days & " & Right(CStr(decNumber - Fix(decNumber)), 1) & " Hours"
    Debug.Print , "dec OUT", decNumber, DH_Notes
    Debug.Print ""
    
'' Method 3
    strLabel = "Sess M2"
    decNumber = intHours / 8
    Debug.Print strLabel, "DecNumber", decNumber
    decNumber = ((DatePart("n", decNumber) / 60) + DatePart("H", decNumber))
    DH_Notes = CStr(Fix(decNumber)) & " Days & " & Right(CStr(decNumber - Fix(decNumber)), 1) & " Hours"
    Debug.Print , "dec OUT", decNumber, DH_Notes
    Debug.Print ""
    
'' Method 4
    strLabel = "DCB"
    decNumber = intHours / 8
    Debug.Print strLabel, "DecNumber", decNumber
    decNumber = Fix(decNumber) & "." & ((decNumber * 8) - (Fix(decNumber) * 8)) '' Expanded to only use decNumber
    DH_Notes = CStr(Fix(decNumber)) & " Days & " & Right(CStr(decNumber - Fix(decNumber)), 1) & " Hours"
    Debug.Print , "dec OUT", decNumber, DH_Notes
    Debug.Print ""
    
    Next X
    
End Sub

 

[RuralGuy]

Boy was I all wet on my response. I completely missed the Days/Hours part of the OP. Sorry folks.

 

[dcb]

RuralGuy: It seems that no one else is understanding what I am saying

Oh well....

 

[RuralGuy]

You can lead a horse to water but...

 

[Steve R.]

It seems that no one else is understanding what I am saying

It's always good to question the validity of code and test it out.

 

[dcb]

It's always good to question the validity of code and test it out.

So you agree that truncate does not work for all possible cases?

 

[Steve R.]

So you agree that truncate does not work for all possible cases?

No - I did not test your scenarios out. I was giving you deserved credit for taking the extra work to evaluate the various solutions and determining the best solution.

 

[dcb]

No - I did not test your scenarios out. I was giving you deserved credit for taking the extra work to evaluate the various solutions and determining the best solution.

Cheers - However it was all about helping the OP

I wonder how much money etc goes missing every year because of round, truncate, hours -> days conversions in accounting type db's?

 

[Galaxiom]

I wonder how much money etc goes missing every year because of round, truncate, hours -> days conversions in accounting type db's?

Remember that movie where a bank employee transferred all the penny roundings to his own account?

Incidentally it is important to realise that the Round function in Access uses Banker's Rounding which favours an even least significant digit in the result where the input is in the middle. Both 1.5 and 2.5 round to 2.

Over a large number of roundings the errors tend to cancel out unlike the irrational "always round 0.5 up" system many of us were taught at school.

 

[Rich]

It's a very generous company that gives 9hrs sick as less than a day

 

[gemma-the-husky]

rounding 6.375 down

both int or fix will do this - they work differently with negative numbers, thats all

so

int(6.375*10)/10 will give 6.3 and
fix(6.375*10)/10 will ALSO give 6.3


------------
i see what dcb is getting at though

hours/minutes are fractional days and probably cannot be handled just as normal decimal values