vba - Using Excel's Rounddown in a UDF rounds 1 to 0 -

to make long story short, place work measures time quadrants of clock. instance, 1.1 hour , 0-15 minutes, 1.2 hour , 15-30 minutes, , 1.3 hour , 30-45 minutes. there no 1.4 because 1.4 of course equal 2.

i wanted make excel sheet automatically add time under system, wrote udf convert times separating decimal values , multiplying 2.5 normal decimal value (.1 = .25, .2 = .5, .3 = .75) , dividing 2.5 @ end convert employer's format. i'm aware can done using excel's existing formulas, is kind of messy , honest i'm stubborn let go now.

if @ screenshot below you'll see function works of columns except final weekly total column reason displays 39.4 instead of 40 (again 2 values technically equivalent, program not converting .4 1 reason).

http://i.imgur.com/yxovlkp.png

here code in it's entirety. problem seems occur when remainder becomes equal 1 (for simplicity imagine 2 values ending .2 entered) , rounded 0 somehow @ end.

function newmath(week range) double  dim time variant dim remainder double dim wholetime double  remainder = 0 wholetime = 0  each time in week   remainder = remainder + ((time - worksheetfunction.rounddown(time, 0)) * 2.5)   'separate , sum decimal values   wholetime = wholetime + worksheetfunction.rounddown(time, 0)                    'separate , sum whole hours next time  'problem occurs @ point when remainder = 1 'worksheetfunction.rounddown(remainder, 0) equal 0 below when 1 should round down 1  wholetime = wholetime + worksheetfunction.rounddown(remainder, 0)                 'add whole remainder hours whole time remainder = (remainder - worksheetfunction.rounddown(remainder, 0)) / 2.5         'get decimal value of remainder , convert quadrant  newmath = wholetime + remainder  end function 

somehow when remainder equals 1 excel's rounddown function seems round 0.

that means following line not add 1 whole number times should:

wholetime = wholetime + worksheetfunction.rounddown(remainder, 0)   

and line return 1 gets divided 2.5 when shouldn't (which .4 comes from):

remainder = (remainder - worksheetfunction.rounddown(remainder, 0)) / 2.5  

i'm not sure what's going on or why excel rounding remainder of 1 0 if indeed problem. appreciate @ , let me know if need more information. thanks!

here's different way of doing things not wind 0.3999999 in place of 4. note used vba int function should execute more rapidly worksheetfunction.roundown(n,0).

by multiplying time * 10, , then, mod function summing last digit, can integer math until time convert final result.

also note below routine designed positive numbers only. if may having negative numbers on time sheet, should use fix in place of int

option explicit  function newmath(week range) double  dim time range dim remainder variant dim wholetime long  remainder = 0 wholetime = 0  each time in week     wholetime = wholetime + int(time)     remainder = remainder + (time * 10) mod 10 next time  'problem occurs @ point when remainder = 1 'worksheetfunction.rounddown(remainder, 0) equal 0 below when 1 should round down 1  wholetime = wholetime + int(remainder / 4)               'add whole remainder hours whole time remainder = ((remainder / 4) - int(remainder / 4)) / 2.5  'get decimal value of remainder , convert quadrant  newmath = wholetime + remainder  end function