WeekNum ISO
 

WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start from
January 3. The first week should contain the first Thursday in that year and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

Hi
see:
http://cpearson.com/excel/weeknum.htm
and
http://www.dicks-blog.com/archives/2...t-2/trackback/

"RPitoyo" wrote:

WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start from
January 3. The first week should contain the first Thursday in that year and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

I myself use this one:
=IF(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=0;INT(((DATE(YEAR(A1)
-1;12;31)-DATE(YEAR(A1)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(A1)-1;12;31);2)+4)/7);
IF(AND(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=53;WEEKDAY(DATE(YE
AR(A1);12;31
);2)<4);1;INT((A1-DATE(YEAR(A1);1;1) +1+7-WEEKDAY(A1;2)+4)/7)))


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"RPitoyo" wrote in message
...
WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start
from
January 3. The first week should contain the first Thursday in that year
and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

Hi Arvi,

With a date in A1:

=INT((A1-(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)-
WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)))+5)/7)

or
=1+INT(MIN(MOD(A1-DATE(YEAR(A1)+{-1;0;1},1,5)+
WEEKDAY(DATE(YEAR(A1)+{-1;0;1},1,3)),734))/7)

or (calendar 1900 specific)
=INT(MOD(INT((A1-2)/7)+0.6,52+5/28))+1

Regards,

Daniel M.

"Arvi Laanemets" wrote in message
...
I myself use this one:
=IF(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=0;INT(((DATE(YEAR(A1)
-1;12;31)-DATE(YEAR(A1)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(A1)-1;12;31);2)+4)/7);
IF(AND(INT((A1-DATE(YEAR(A1);1;1)+1+7-WEEKDAY(A1;2)+4)/7)=53;WEEKDAY(DATE(YE
AR(A1);12;31
);2)<4);1;INT((A1-DATE(YEAR(A1);1;1) +1+7-WEEKDAY(A1;2)+4)/7)))


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"RPitoyo" wrote in message
...
WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start
from
January 3. The first week should contain the first Thursday in that year
and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.

On Mon, 20 Dec 2004 02:35:01 -0800, "RPitoyo"
wrote:

WeekNum resulting non ISO compliant result.
for examle WeekNum("01/01/2005",2) results 1. According to ISO, 1 January
2005 should be week 53 of 2004 and then first week of January 2005 start from
January 3. The first week should contain the first Thursday in that year and
always start from Monday.
Is there any function that can return the correct week number according to
ISO?
Is there any reverse function of WeekNum which return the correct starting
date of the given week?
Thanks in advance.


You can use this UDF:

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

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 into the window that opens.

To use it:

=ISOWeeknum(date) will give the ISO compliant weeknumber for any date. 'date'
may be a cell reference.


--ron