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;

Please Login or Register  to view this content.

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;

Please Login or Register  to view this content.

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"gti_jobert" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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

[[email protected]]

On Wed, 15 Mar 2006 09:36:58 -0600, gti_jobert
<[email protected]> 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?

[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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"gti_jobert" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"gti_jobert" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[gti_jobert]

Thanks for all your replys!

I have now substituted and using the function:

Please Login or Register  to view this content.

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

Any ideas???

[gti_jobert]

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