Week Number Function

[gti_jobert]

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

 

[Ron de Bruin]

Hi gti_jobert

Look here
http://www.rondebruin.nl/weeknumber.htm

 

[Guest]

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?

 

[gti_jobert]

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

 

[Ron de Bruin]

On Chip's site there is a function for the Excel weeknum
*******************************************

You can achieve the same result in VBA code with the following function:

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

The FW parameter serves the same purpose here as it does in the WEEKNUM worksheet function. Set it to 1 to indicate that weeks
begin on Sunday, or to 2 to indicate that weeks begin on Monday.


For ISO there is on on my site
I use them here also
http://www.rondebruin.nl/calendar.htm

 

[gti_jobert]

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

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