MOD problem

[Guest]

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.

 

[keepITcool]

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 :