Rounding to the nearest .5

[Dave_cha]

Hi folks,

I'm trying to build a pro-rata annual leave calculator based on the starting date of an employee.

I have it working but it's rounding the result to the nearest whole day. HR have asked that it calculates to the nearest half day.

By way of an example, an employee who started earlier this year had a pro-rata annual leave allowance of 20.6 days. This is currently being rounded to 21 days whereas HR would prefer it to round to 20.5 days.

Does anyone have a function that would work here?

Thanks,

Dave

 

[Mile-O]

=Int([YourValue]+0.5)

 

[Dave_cha]

But what of the value is, for example, 2.9? In this case I would be looking for 3 come back whereas if it where 2.6 I would be looking for 2.5 to come back....i.e. the nearest .5.

Int(2.6 + .5) = 3 (Should be 2.5)
Int(2.9 + .5) = 3 (Correct)
Int(2.6) + .5 = 2.5 (Correct)
Int(2.9) + .5 = 2.5 (Should be 3)

Rgd's,

Dave

 

[Mile-O]

Sorry, thought you wanted to round up.

 

[Dave_cha]

Just figured out the solution;

1. Calculate the pro-rata value (PRVAL)..e.g 2.6
2. Declare an int (INTVAL) and set it equal to int([PRVAL])...eg INT(2.6) = 2
3. Subtract INTVAL from the PRVAL...e.g. 2.6-2 = .6 (declare as double).
4. Run a select statement using the return from the above subtraction and compare to 3 cases...(a) Less than .25 (b) Between .25 and .75 (c) Greater than .75
5. Set PRVAL value equal to INTVAL, INTVAL+.5 or INTVAL +1 depending on which of the above conditions is true.

Will post the function when complete but it's fairly straight forward.....sometimes you can't see the wood for the trees.

Dave

 

[MarkK]

Function Round05(sValue As Single) As Single
``Round05 = Int(sValue * 2 + 0.5) / 2
End Function

 

[Dave_cha]

Thanks Iagbolt...much better solution.

 

[Sergeant]

Damned ingenious, Lagbolt.
Works in Excel cells, as well!

Sarge.

 

[Matrix_zero]

"Damned ingenious, Lagbolt. "


I agree!