# Week Number Function

G

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

G

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

G

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

R

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

G

#### gti_jobert

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

G

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