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.