Function WEEKNUM does not show correct result for 15.2.2005

[Edward]

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.

 

[Ron de Bruin]

Hi Edward

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

 

[Ron Rosenfeld]

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

 

[Ron de Bruin]

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

 

[Ron Rosenfeld]

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

 

[Ron de Bruin]

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.

 

[Ron Rosenfeld]

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

 

[Ron de Bruin]

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

 

[Ron Rosenfeld]

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

 

[Edward]

The script works perfect, Thanks
Edward

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

 

[Ron Rosenfeld]

The script works perfect, Thanks
Edward

You're welcome. Thanks for the feedback.

--ron