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

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

On Wed, 15 Mar 2006 09:36:58 -0600, gti_jobert
wrote:


Hi all,

I am trying to calculate the week number on a given date. The following
function I obtained from the net;
How do you define week number? For example, Jan 1, 2003 was on Wednesday.
In 2003 is the first day of week 2:
Wednesday Jan 8, or
Sunday Jan 5?

Hi,

I have searched through your site before whilst hunting for a solution
to this, theres a calendar .xls but thats no good for me, nor are the
formulas as I need a solution via VBA.

TIA


--
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

Thanks for all your replys!

I have now substituted and using the function:


Code
-------------------

Function VBAWeekNum(D As Date, FW As Integer) As Integer
VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

-------------------


But I am still getting the ByRef mismiatch error, the date in the cel
is stored as text and in format 16032006.

Any ideas??

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52266

wooops, have just got it working......using that function I split th
date with a "/" and made sure I declared it as date

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52266