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.

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.