Posted to microsoft.public.excel.programming
|
|
Week Number Function
Hi gti_jobert
Look here
http://www.rondebruin.nl/weeknumber.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl
"gti_jobert" wrote in message
...
Hi all,
I am trying to calculate the week number on a given date. The following
function I obtained from the net;
Code:
--------------------
Function WEEKNR(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
WEEKNR = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
B = Year(InputDate + ((8 - A) Mod 7) - 3)
C = DateSerial(B, 1, 1)
D = (Weekday(C, vbSunday) + 1) Mod 7
WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1
End Function
--------------------
This function seems to output the correct value when i do *MsgBox
WEEKNR(Date)* but when i substitute the excel date function for a date
of my own it seems to give me a ByRef error.
The date in the cell is in format 15032006 and i was going the
following;
Code:
--------------------
Dim dateget as date
dateget = Left(Cells(7, 8).Value, 2) & "/" & _
Mid(Cells(7, 8).Value, 3, 2) & "/" & Right(Cells(7, 8).Value, 4)
--------------------
and the error comes when i do *MsgBox WEEKNR(dateget )*
Any ideas guys, loosing my mind today! Thanks
--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=522661
|