Function WEEKNUM does not show correct result for 15.2.2005

As a result Excel 2003 shows 8 (week number) for 15.2.2005, but the correct
result is 7.
For Year 2004 the answer is correct.

--
Edward

Hi Edward

See my site and chip's site for information about this
http://www.rondebruin.nl/weeknumber.htm

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



"Edward" <[email protected]> wrote in message news:[email protected]...
> As a result Excel 2003 shows 8 (week number) for 15.2.2005, but the correct
> result is 7.
> For Year 2004 the answer is correct.
>
> --
> Edward

On Fri, 11 Mar 2005 04:55:04 -0800, Edward <[email protected]>
wrote:

>As a result Excel 2003 shows 8 (week number) for 15.2.2005, but the correct
>result is 7.
>For Year 2004 the answer is correct.

Actually, if you read the documentation in HELP, you will see that the Excel
WEEKNUM function does NOT give the ISO week number, which is probably what you
are expecting. So to talk about "correct" you must first define what your
standards are. WEEKNUM in Excel does give the "correct" answer -- at least it
complies with it's own definition which is NOT the SAME definition as the ISO
standard.

If you want to obtain an ISO compliant week number, I think the easiest way is
to use either a VBA User Defined Function or, if you have Longre's morefunc.xll
add-in, you can use his ISO.WEEKNUM function.

Below is a VBA routine. To enter it, <alt-F11> opens the VB Editor. Ensure
your project is highlighted in the project explorer window, then Insert/Module
and paste the code below into the window that opens.

You can then use =isoweeknum(date) in your workbook.

================================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
===============================


--ron

For the OP: See also the function on my site that don't need the UDF



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



"Ron Rosenfeld" <[email protected]> wrote in message news:[email protected]...
> On Fri, 11 Mar 2005 04:55:04 -0800, Edward <[email protected]>
> wrote:
>
>>As a result Excel 2003 shows 8 (week number) for 15.2.2005, but the correct
>>result is 7.
>>For Year 2004 the answer is correct.
>
> Actually, if you read the documentation in HELP, you will see that the Excel
> WEEKNUM function does NOT give the ISO week number, which is probably what you
> are expecting. So to talk about "correct" you must first define what your
> standards are. WEEKNUM in Excel does give the "correct" answer -- at least it
> complies with it's own definition which is NOT the SAME definition as the ISO
> standard.
>
> If you want to obtain an ISO compliant week number, I think the easiest way is
> to use either a VBA User Defined Function or, if you have Longre's morefunc.xll
> add-in, you can use his ISO.WEEKNUM function.
>
> Below is a VBA routine. To enter it, <alt-F11> opens the VB Editor. Ensure
> your project is highlighted in the project explorer window, then Insert/Module
> and paste the code below into the window that opens.
>
> You can then use =isoweeknum(date) in your workbook.
>
> ================================
> Function ISOWeeknum(dt As Date) As Integer
> ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
> If ISOWeeknum > 52 Then
> If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
> ISOWeeknum = 1
> End If
> End If
> End Function
> ===============================
>
>
> --ron

On Fri, 11 Mar 2005 14:28:21 +0100, "Ron de Bruin" <[email protected]>
wrote:

>For the OP: See also the function on my site that don't need the UDF

Your function works perfectly, of course. I was just putting forth a different
method.

Since calculation speed is not an issue for me when using this function, I
personally prefer having a UDF in my personal.xla addin file; because it's
easier for me to remember a function like =ISOWEEKNUM(dt) than the equivalent
formula.

But certainly my "preference" is not meant to imply that it is any better than
any other approach, except for satisfying my own personal bias.

Best wishes,
--ron

Hi Ron

The problem is if you use the UDF that if you send the workbook to someone else
you must add the UDF to the workbook.
Norman I am making a new Add-in on this moment that insert formulas in data tables and
we choose not to use ATP and UDF's because of this problem and also the international problem with ATP.



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



"Ron Rosenfeld" <[email protected]> wrote in message news:[email protected]...
> On Fri, 11 Mar 2005 14:28:21 +0100, "Ron de Bruin" <[email protected]>
> wrote:
>
>>For the OP: See also the function on my site that don't need the UDF
>
> Your function works perfectly, of course. I was just putting forth a different
> method.
>
> Since calculation speed is not an issue for me when using this function, I
> personally prefer having a UDF in my personal.xla addin file; because it's
> easier for me to remember a function like =ISOWEEKNUM(dt) than the equivalent
> formula.
>
> But certainly my "preference" is not meant to imply that it is any better than
> any other approach, except for satisfying my own personal bias.
>
> Best wishes,
> --ron

On Fri, 11 Mar 2005 18:43:03 +0100, "Ron de Bruin" <[email protected]>
wrote:

>The problem is if you use the UDF that if you send the workbook to someone else
>you must add the UDF to the workbook.

Is that hard to do? I thought if one added the UDF to the workbook, it would
be saved along with the workbook.


--ron

Hi Ron

1) What if people send only one sheet.
2) what if they want to use it in a other workbook
Most people don't know how to copy a UDF in a module

3)what if they copy it in there personal.xls(like you)

With the Add-in we try to make it easy to insert the formulas in a sheet
We have no problems then we hope<g>

Also we not use the weeknum functions but others because as you know it
will not translate the weeknum function to a other language.

Also the Text function you can't use international
Y for year is in Dutch j for example



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



"Ron Rosenfeld" <[email protected]> wrote in message news:[email protected]...
> On Fri, 11 Mar 2005 18:43:03 +0100, "Ron de Bruin" <[email protected]>
> wrote:
>
>>The problem is if you use the UDF that if you send the workbook to someone else
>>you must add the UDF to the workbook.
>
> Is that hard to do? I thought if one added the UDF to the workbook, it would
> be saved along with the workbook.
>
>
> --ron

On Fri, 11 Mar 2005 22:47:37 +0100, "Ron de Bruin" <[email protected]>
wrote:

>Hi Ron
>
>1) What if people send only one sheet.
>2) what if they want to use it in a other workbook
>Most people don't know how to copy a UDF in a module
>
>3)what if they copy it in there personal.xls(like you)
>
>With the Add-in we try to make it easy to insert the formulas in a sheet
>We have no problems then we hope<g>
>
>Also we not use the weeknum functions but others because as you know it
>will not translate the weeknum function to a other language.
>
>Also the Text function you can't use international
>Y for year is in Dutch j for example

All good points. Just a different approach.


--ron

The script works perfect, Thanks
Edward

"Ron Rosenfeld" wrote:

> On Fri, 11 Mar 2005 04:55:04 -0800, Edward <[email protected]>
> wrote:
>
> >As a result Excel 2003 shows 8 (week number) for 15.2.2005, but the correct
> >result is 7.
> >For Year 2004 the answer is correct.
>
> Actually, if you read the documentation in HELP, you will see that the Excel
> WEEKNUM function does NOT give the ISO week number, which is probably what you
> are expecting. So to talk about "correct" you must first define what your
> standards are. WEEKNUM in Excel does give the "correct" answer -- at least it
> complies with it's own definition which is NOT the SAME definition as the ISO
> standard.
>
> If you want to obtain an ISO compliant week number, I think the easiest way is
> to use either a VBA User Defined Function or, if you have Longre's morefunc.xll
> add-in, you can use his ISO.WEEKNUM function.
>
> Below is a VBA routine. To enter it, <alt-F11> opens the VB Editor. Ensure
> your project is highlighted in the project explorer window, then Insert/Module
> and paste the code below into the window that opens.
>
> You can then use =isoweeknum(date) in your workbook.
>
> ================================
> Function ISOWeeknum(dt As Date) As Integer
> ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
> If ISOWeeknum > 52 Then
> If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
> ISOWeeknum = 1
> End If
> End If
> End Function
> ===============================
>
>
> --ron
>

On Mon, 14 Mar 2005 00:07:04 -0800, Edward <[email protected]> wrote:

>The script works perfect, Thanks
>Edward

You're welcome. Thanks for the feedback.

--ron