To make a long story short, the place where I work measures time by quadrants of a clock. For instance, 1.1 is an hour and 0-15 minutes, 1.2 is an hour and 15-30 minutes, and 1.3 is an hour and 30-45 minutes. There is no 1.4 because 1.4 is of course equal to 2.
I wanted to make an excel sheet that would automatically add my time under this system, so I wrote this UDF to convert the times by separating the decimal values and multiplying by 2.5 to get a normal decimal value (.1 = .25, .2 = .5, .3 = .75) and then dividing by 2.5 at the end to convert back to my employer's format. I'm aware that it can be done using excel's existing formulas, but is is kind of messy and to be honest I'm too stubborn to let this go now.
If you look at the screenshot below you'll see that the function works for all of the columns except the final weekly total column for some reason which displays 39.4 instead of 40 (again the two values are technically equivalent, but the program is not converting the .4 into a 1 for some reason).
https://i.sstatic.net/PEBjJ.png
Here is the code in it's entirety. The problem seems to occur when the remainder becomes equal to exactly 1 (for simplicity just imagine that two values ending .2 are entered) and then is rounded to zero somehow at the end.
Function newMath(week As Range) As Double
Dim time As Variant
Dim remainder As Double
Dim wholeTime As Double
remainder = 0
wholeTime = 0
For Each time In week
remainder = remainder + ((time - WorksheetFunction.RoundDown(time, 0)) * 2.5) 'Separate and sum up decimal values
wholeTime = wholeTime + WorksheetFunction.RoundDown(time, 0) 'Separate and sum up whole hours
Next time
'Problem occurs at this point when remainder = 1
'WorksheetFunction.RoundDown(remainder, 0) will equal 0 below even when 1 should round down to 1
wholeTime = wholeTime + WorksheetFunction.RoundDown(remainder, 0) 'Add the whole remainder hours to whole time
remainder = (remainder - WorksheetFunction.RoundDown(remainder, 0)) / 2.5 'Get decimal value of remainder and convert back to quadrant
newMath = wholeTime + remainder
End Function
Somehow when the remainder equals exactly 1 excel's rounddown function seems to round it to 0.
That means that the following line does not add the 1 to the whole number times as it should:
wholeTime = wholeTime + WorksheetFunction.RoundDown(remainder, 0)
And that this line will return a 1 which gets divided by 2.5 when it shouldn't (which is where the .4 comes from):
remainder = (remainder - WorksheetFunction.RoundDown(remainder, 0)) / 2.5
I'm not exactly sure what's going on or why excel is rounding my remainder of 1 to 0 if that is indeed the problem. I appreciate any help at all and let me know if you need any more information. Thanks!
Here's a slightly different way of doing things so as not to wind up with the 0.3999999 in place of the 4. Note that I used the VBA Int function which should execute more rapidly than the worksheetfunction.roundown(n,0).
By multiplying time * 10, and then, with the Mod function summing the last digit, we can do integer math until it is time to convert back to the final result.
Also note that the below routine is designed for positive numbers only. If you may be having negative numbers on your time sheet, you should use Fix in place of Int
Option Explicit
Function newMath(week As Range) As Double
Dim time As Range
Dim remainder As Variant
Dim wholeTime As Long
remainder = 0
wholeTime = 0
For Each time In week
wholeTime = wholeTime + Int(time)
remainder = remainder + (time * 10) Mod 10
Next time
'Problem occurs at this point when remainder = 1
'WorksheetFunction.RoundDown(remainder, 0) will equal 0 below even when 1 should round down to 1
wholeTime = wholeTime + Int(remainder / 4) 'Add the whole remainder hours to whole time
remainder = ((remainder / 4) - Int(remainder / 4)) / 2.5 'Get decimal value of remainder and convert back to quadrant
newMath = wholeTime + remainder
End Function