Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MOD problem
I have a problem which I do not understand. The result of the logic hereunder
would be to be 20. However I get 39 Why ??? Date = 5/19/2005 - 5498.6 Sub testWR() '----------------------------------------------' Dim nCalcTemp As Double, nWeekNr As Double nCalcTemp = Int((Date - 2) / 7) + 0.6 nWeekNr = nCalcTemp Mod (52 + 5 / 28) End Sub This is equivalent to this (A3 contains 5/19/2005) which give 20 ! =INT(MOD(INT(($A3-2)/7)+0.6,52+5/28))+1 Many Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MOD problem
Dont confuse or equate VBA's MOD operator with the MOD worksheetfunction. VBA's mod operator is intended for longs and integers, and if the arguments are floating points the are *rounded to integers* first. thus nCalcTemp = 5498,6: rounded to 5499 52+5/28 = 52,1785 : rounded to 52 (5499 / 52 - 5499 \ 52) * 52 = 0,75 * 52 = 39! note the use of the \ (backslash) operator for integer division To get 'your' 20 use: Sub testMOD() Dim nCalcTemp As Double, nWeekNr As Double nCalcTemp = (Date - 2) \ 7 + 0.6 nWeekNr = 1 + (28 * nCalcTemp Mod (28 * 52 + 5)) \ 28 End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Fr. Vandecan wrote : I have a problem which I do not understand. The result of the logic hereunder would be to be 20. However I get 39 Why ??? Date = 5/19/2005 - 5498.6 Sub testWR() '----------------------------------------------' Dim nCalcTemp As Double, nWeekNr As Double nCalcTemp = Int((Date - 2) / 7) + 0.6 nWeekNr = nCalcTemp Mod (52 + 5 / 28) End Sub This is equivalent to this (A3 contains 5/19/2005) which give 20 ! =INT(MOD(INT(($A3-2)/7)+0.6,52+5/28))+1 Many Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |